| Lesson 4 | Manipulate tables |
| Objective | Describe enhancements in table management. |
Describe Table Management Enhancements
Table management covers a broad area.
Many related subjects are covered in other courses, such as the
Oracle PL/SQL Programming courses from this website.
This course covers the enhancements that assist the DBA in handling storage issues for tables. The table below lists the enhancements we will review later in this module.
| Enhancement | Description |
| Relocate a table | This capability saves time. You can now move a table from one tablespace to another without having to drop and recreate the table. This command reorganizes the table, eliminating unused space and removing chained rows. |
| Create a temporary table | This enhancement provides special handling for tables that you used for storing intermediate (temporary) data.
Temporary tables don'tget the same backup and recovery treatment as regular tables, which saves overhead time and space. |
| Drop a column | This enhancement allows you to remove unwanted columns from a table
without completely rebuilding the table. This much-neededenhancement streamlines system maintenance. |
The next three lessons expand on each of these enhancements.
The next lesson describes how to relocate a table.
- Relocate a table
- Create a temporary table
- Drop a column
Variable Record Format
A file is in variable record format when the length of each record in a character field is included at the beginning of each record in the datafile.
This format provides some added flexibility over the fixed record format and a performance advantage over the stream record format. For example, you can specify a datafile that is to be interpreted as being in variable record format as follows:
INFILE "datafile_name" "var n"
In this example, n specifies the number of bytes in the record length field. If n is not specified, SQL*Loader assumes a length of 5 bytes. Specifying n larger than 40 will result in an error.
Example 5-4 shows a control file specification that tells SQL*Loader to look for data in the datafile example.dat and to expect variable record format where the record length fields are 3 bytes long.
The example.dat datafile consists of three physical records. The first is specified to be 009 (that is, 9) bytes long, the second is 010 bytes long (that is, 10, including a 1-byte newline), and the third is 012 bytes long (also including a 1-byte newline).
Note that newline characters are not required with the variable record format. This example also assumes a single-byte character set for the datafile.
The lengths are always interpreted in bytes, even if character-length semantics are in effect for the file. This is necessary because the file could contain a mix of fields, some processed with character-length semantics and others processed with byte-length semantics.
Example 5-4: Loading Data in Variable Record Format
load data
infile 'example.dat' "var 3"
into table example
fields terminated by ',' optionally enclosed by '"'
(col1 char(5),
col2 char(7))
example.dat:
009hello,cd,010world,im,
012my,name is,