Table Compression in Oracle

Recently i had to move a schema of size 70GB to another database[Oracle 10.2.0.3]. That was a readonly schema and never had any DML on any of the tables. In the new database i did compress the tables and the size was brought down from 70Gb to 22Gb.

1]Divided the tables based on the size.
2]Created the tablesapces.
3]Pre-created the tables on respective tablespaces.
4]Imported the tables.
5]Compressed and moved big tables from APP_BIG_TABLES_TEMP to APP_BIG_TABLES.
6]Rebuild the primary key indexes of the moved tables.
7]Droped APP_BIG_TABLES_TEMP.
8]Created the indexes.

The tables where create with PCTFREE 0 to take maximum advantage of the space in each extents as i was sure that never an UPDATE would happen on any of these tables.

Compress statement with “move”
======================
ALTER TABLE <table_name> MOVE TABLESPACE <to new tablespace> COMPRESS NOLOGGING PARALLEL (degree N);
eg: ALTER TABLE SALES_INFORMATION MOVE TABLESPACE APP_BIG_TABLES COMPRESS NOLOGGING PARALLEL (degree 8);

COMPRESS alone will only compress the tables, to recalim the space use MOVE in the statement.
My Server had 32 CPUs, but i just used 8. I should leave the rest for other applications. 🙂

To move tables with LOB segments:
ALTER TABLE <TABLE_NAME> MOVE TABLESPACE <new_tablespace> LOB(<lob_column_1) STORE AS (TABLESPACE <new_tablespace) LOB(<lob_column_2) STORE AS (TABLESPACE <new_tablespace);

Why did not i compress and move the table in the same tablespace?
COMPRESS and MOVE of tables in the same tablespace will not bring down the datafiles high water mark and i will not be able to resize the datafiles to a smaller size. So i found it easy to drop the very big APP_BIG_TABLES_TEMP once the tables were compressed and moved to APP_BIG_TABLES.

BEFORE COMPRESS
================
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 7.73681641
TABLE 55 62.6555786
———-
sum 70.392395
AFTER COMPRESS
===============
OBJECT_TYPE COUNT SIZE(in GB)
—————— ———- ———-
INDEX 110 2.375
TABLE 55 19.8398438
———-
sum 22.2148438

Advertisements

3 thoughts on “Table Compression in Oracle

  1. Pingback: Table Compression in Oracle

  2. Pingback: Index « My confrontations with oracle

  3. This is an old thread, but for anyone interested: the trick to moving the High Watermark and re-claiming the space is to:

    (1) do the ALTER TABLE MOVE **twice**. The first time moves it, freeing the space, the second moves it back to the freed-up space.
    (2) Now shrink the tablespace’s datafile

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s