3.3.3 Changing Compression Level

You can change the compression level for a partition, table, or tablespace.

The following example describes a scenario when you might want to change the compression level.

A company uses warehouse compression for its sales data, but sales data older than six months is rarely accessed. If the sales data is stored in a table that is partitioned based on the age of the data, then the compression level for the older data can be changed to archive compression to free up disk space.

• To change the compression level of a partitioned table you can use the DBMS_REDEFINITION package.

  This package performs online redefinition of a table by creating a temporary copy of the table which holds the table data while it is being redefined. The table being redefined remains available for queries and DML statements during the redefinition. The amount of free space for online table redefinition depends on the relative compression level for the existing table, and the new table. Ensure you have enough hard disk space on your system before using the DBMS_REDEFINITION package.

• To change the compression level for a single partition of a partitioned table, you can use the ALTER TABLE ... MODIFY PARTITION command.

• To change the compression level of a non-partitioned table use the ALTER TABLE ... MOVE command with the COMPRESS FOR clause.

  To perform DML statements against the table while the ALTER TABLE ... MOVE command is running, you must also add the ONLINE clause.

• To change the compression level for a tablespace, use the ALTER TABLESPACE command.

  This defines the default for new objects created in the tablespace. Existing objects are not changed or moved.

• You can use Automatic Data Optimization (ADO) to create policies that automatically adjust the compression level.