Regular Table Operations

You will, doubtless, at some point need to reorganize a table, move a table from one tablespace to another, redefine an entire table or portions of tables, or perform other interesting tasks. This section discusses these operations.

Reorganizing Tables

Reorganizing tables can eliminate row migration both offline (as you have been able to historically) as well as online.

Redefining Tables

Occasionally you will need to redefine a table that is in production. Historically, you would have needed to lock the table in shared mode to perform this action. In effect, this shuts out all DML access. In Oracle 9i, you can now redefine tables while the database is online, and the table is accessible to DML operations for the duration of the process.

During the redefinition process you can

• Add or rename columns

• Drop columns that are not part of the primary key

• Convert between regular table and partitioned table and back to regular table

• Re-create the table to reduce block level fragmentation

• Add the parallel query capability

• Change the storage parameters

There are, naturally, some restrictions for online redefinition. These include

• The table in question must have a primary key.

• The source table to be redefined and the target table must have identical primary key columns.

• The table cannot have either a BFILE column or a LONG column.

• The table can have LOB columns.

• The table cannot have any user-defined types.

• The table cannot be a part of a cluster.

• The table cannot be an overflow table for an index-organized table.

• The table cannot belong to either SYS or SYSTEM.

• The table cannot have any materialized views or materialized view logs defined on it.

• The new column cannot be declared as NOT NULL.

• Redefinition must belong to the same schema.

Redefinition Process

The steps necessary to perform the online redefinition are as follows:

1.	Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure of the DBMS_REDEFINTION package.
2.	Create the interim table with the same schema definition and characteristics as the source table that you want to redefine.
3.	Start the redefinition process by invoking the START_REDEF_TABLE procedure in the DBMS_REDEFINITION package: DBMS_REDEFINITION.START_REDEF_TABLE ('myschema', 'address', 'redef_address'); Optionally, you can enter column mappings as an additional argument. If the column mappings are not provided, Oracle assumes that the columns are the same names in both tables.
4.	Create any indexes, constraints, triggers, and grants on the target table that are created on the source table, but create them with different names. Any referential integrity constraints have to be created on this interim table with the DISABLE option. Upon successful completion of the redefinition, Oracle enables these constraints.
5.	Periodic synchronization of the interim table with the original can be done, particularly if many DML operations are being done against the original. This can be done with the DBMS_REDEFINITION.SYNC_INTERIM_TABLE package procedure.
6.	Complete the redefinition by invoking the FINISH_REDEF_TABLE procedure of the DBMS_REDEFINTION package. At this point, the table is acquired with an exclusive lock, and there can be no active DML occurring against the table at this point. During this step, the original table is redefined with all the characteristics, indexes, constraints, triggers, and grants that you defined on the interim table, and all referential constraints are enabled.
7.	Optionally, you can rename the indexes.

If you encounter any errors or if you want to abort the redefinition for any reason, you must invoke the ABORT_REDEF_TABLE procedure in the DBMS_ REDEFINITION package.

Moving Tables

In Oracle 9i, you can now move nonpartitioned tables from one tablespace to another without the need to perform an export/import operation. After moving a table, you will have to rebuild the indexes to avoid having the indexes be in an unusable state. In this way, you can segregate hot tables off to their own tablespaces or distribute I/O over more devices. The command used to move a table to another tablespace is as follows:

 ALTER TABLE addresses MOVE TABLESPACE mydata2; 

Truncating Tables

If you want to remove all the data from a table, lower the high water mark, and still retain the definition of the table, you can issue the trUNCATE command to accomplish this:

 TRUNCATE TABLE addresses; 

You can, optionally, drop or reuse the storage associated with the truncated table to undefine the segments associated with the table.

Because trUNCATE TABLE is a Data Definition Language (DDL) command, no undo is generated. Therefore, it is important that you be sure that you really want to truncate any table before you issue the command. Rolling back will not get the data back. For this same reason, ON DELETE TRiggers don't fire on a TRUNCATE command.

The indexes that correspond to the table being truncated are also truncated (no data, no indexes).

You cannot truncate a table being referenced by a foreign key.

Dropping Tables

If you simply want to remove the entire table from the database, you can issue the DROP TABLE command:

 DROP TABLE addresses; 

Again, remember that you cannot simply drop a table being referenced by a foreign key.

We have now created a table, manipulated the table at the table level, and determined the way to proceed with some of the common maintenance that might need to occur on a table while that table is online. Now let's examine what can occur at the column level concerning maintenance.

When the table is dropped, the extents used by the table are all released.

Optionally, you can add the CASCADE CONSTRAINTS option if the table is the parent in a foreign key relationship.

You can, similarly, make alterations to tables themselves. The following section addresses many of the operations that you can perform on the table's structure.