Shrink Database Objects

Posted: June 14, 2012 in Objects

In Oracle10g and onwards, we have the option to shrink a segment that helps to manage space more efficiently.  Shrink operations can be performed only on segments in locally managed tablespaces with ASSM.

The segment advisor will highlight any segments that can be shrunk and then you can do the following steps to shrink the object to free up space in the tablespace.

Example

Enable row movement for the table.

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL ENABLE ROW MOVEMENT;

Table altered.

Shrink table but don’t want to shrink HWM

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL SHRINK SPACE COMPACT;

Table altered.

Shrink table and HWM too

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL SHRINK SPACE;

Table altered.

Disable row movement for the table

SYS@test> ALTER TABLE TEST.TEST_AUDIT_DETAIL DISABLE ROW MOVEMENT;

Table altered.

See oracle note: 242090.1 for more options and additional detail

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s