Wednesday, January 14, 2009

Using the 10G Shrink Command

Oracle10G objects that reside in Automatic Segment Space Managed tablespaces can be shrunk using the "ALTER………SHRINK" statement. The shrink statement is not limited to just heap tables and indexes, Oracle also allows index-organized tables, partitions, subpartitions, materialized views and materialized view logs to be shrunk using ALTER commands. In addition, the CASCADE option can be used to propagate the shrink operation to all dependent objects except materialized views, LOB indexes, IOT mapping tables and overflow segments.

It is interesting to note that because the shrink operation may change the ROWIDS in heap-organized tables, row movement must first be enabled by executing the following command:

ALTER TABLE < tablename> ENABLE ROW MOVEMENT;

The shrink operation can be performed while the table is on-line and does not require any extra database data file space. The shrink operation itself is performed by the database internally executing INSERT and DELETE statements. Since the data itself is not changed (just rearranged), DML triggers are not fired during shrink operations.

The shrink operation will free unused space both above and below the high water mark. Rows are moved from one block to another which is why row movement must be enabled on the table beforehand. Unlike the ALTER TABLE....MOVE statement, indexes do not need to be rebuilt after the shrink operation is executed.

alter table ... shrink is subject to the following restrictions:

· You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
· Segment shrink is not supported for tables with function-based indexes or bitmap join indexes. · This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
· You cannot specify this clause for a compressed table.
· You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.
· Segment shrink is not supported for tables with Domain indexes.

No comments:

Post a Comment