Thinking Smaller: The Shrink Command.

The question comes up often and is a good one to be asked; how can you reduce the wasted space in an Oracle standard table? There are different approaches that can be used. You could for example use an export and import or datapump process. However, there are easier and more efficient ways to accomplish a reorganization of tables, thereby reclaiming wasted space.

Some would use a CTAS to get the job done. Granted a CTAS is typically a fast solution because of the parallel feature; however, you lose all your grants, ects. And if it was a partitioned table you would lose your partitions using a “Create Table AS” statement as well.

I like using the “alter table” shrink command. The “shrink” command came along in Oracle release 10. You can’t use if for UNDO or Temporary segments. Neither will it work on Clustered tables. Nor tables with LONG datatypes. Tables with Materialized Views will also have problems.

To make this work you have to first issue an “alter table” statement allowing rows to be moved in the table you want to “shrink”. This is because rows normally when created within the base are assigned a permanent rowid that will be unique and will never change during the life of the row. However, as I stated, “normally”. This alter statement will override that normalcy. It also will require a row level lock to accomplish this. Once this is done, then the “alter table shrink” statement can be issued. The “shrink” statement moves the High Watermark. As you would expect, Oracle will require a table lock for this operation. You can also add a “cascade” to the “alter” statement if you want to include dependent objects.

Here is an example of this process:

TABLE_OPTOUT_TMP (table name) 4,309,647,360 (size in bytes)

15:25:55 SQL> alter table OWNER.TABLE_OPTOUT_TMP enable row movement;

Table altered.

Elapsed: 00:00:00.03

15:26:35 SQL> alter table OWNER.TABLE_OPTOUT_TMP shrink space;

Table altered.

Elapsed: 00:00:06.55

WIRELESS_DNC_OPTOUT_TMP 20,971,520

As you can see, we went from 4.3 gbs to 21 mbs in 7 seconds.

Nice… now you give it a try.

How about a list of tables to do this great little exercise on. Glad you asked. You can do a simple query against dba_advisor_findings as follows:

Select distinct message from dba_advisor_findings where message like '%shrink%';

This should return something that looks similar to this:

“Enable row movement of the table OWNER.TABLE_NAME and perform shrink, estimated savings is 21404475 bytes.”

Enjoy.