Using the Oracle Shrink Command
Introduction
The segment shrink operation reclaims unused space from above and below the high water mark. Unlike space de-allocation which only reclaims space above the high water mark, the shrink operation compacts the object and adjusts the high water mark before releasing the unused space.
Segment shrink is an online operation which allows DML operations to be issued during the data movement phase of the shrink. However, DML operations are blocked for a short time at the end of the shrink operation when the space is de-allocated. Indexes are maintained during the shrink and remain usable after the shrink is complete.
The following example shows the shrink command in operation.
Known Issues
Under some patch revions of version 10gR2 (11g untested), the alter table <tablke name> shrink space does not seem to do anything. In order to perform a shrink operation on a table the commands MUST be issued in the following order; alter table <table_name> shrink space compact followed by alter table <table_name> shrink space
Example
sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 1 20:47:26 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @cr_test_table.sql
Enter the length of your padding value? (if Required):100
Enter the length of your random string value? 100
Enter the number of rows in the table? (Max Values is limited by the rows in all_objects) :5000
old 4: dbms_random.string('X',&ran*dbms_random.value) strval,
new 4: dbms_random.string('X',100*dbms_random.value) strval,
old 5: rpad('x',&pad,'x') padding
new 5: rpad('x',100,'x') padding
old 9: rownum <=&rnums
new 9: rownum <=5000
Table created.
SQL> analyze table TESTING_TABLE compute statistics;
Table analyzed.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
5088
SQL> delete from testing_table where id between &id1 and &id2;
Enter value for id1: 501
Enter value for id2: 1000
old 1: delete from testing_table where id between &id1 and &id2
new 1: delete from testing_table where id between 501 and 1000
500 rows deleted.
SQL> /
Enter value for id1: 2001
Enter value for id2: 2500
old 1: delete from testing_table where id between &id1 and &id2
new 1: delete from testing_table where id between 2001 and 2500
500 rows deleted.
SQL> /
Enter value for id1: 3751
Enter value for id2: 4250
old 1: delete from testing_table where id between &id1 and &id2
new 1: delete from testing_table where id between 3751 and 4250
500 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table TESTING_TABLE compute statistics;
Table analyzed.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
5088
SQL> alter table testing_table enable row movement;
Table altered.
SQL> alter table testing_table shrink space compact;
Table altered.
SQL> analyze table TESTING_TABLE compute statistics;
Table analyzed.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
5088
SQL> alter table testing_table shrink space;
Table altered.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
5088
SQL> analyze table TESTING_TABLE compute statistics;
Table analyzed.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
561
SQL> drop table testing_table;
Table dropped
Bug/Feature?
sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 1 21:07:26 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> @cr_test_table.sql
Enter the length of your padding value? (if Required):100
Enter the length of your random string value? 100
Enter the number of rows in the table? (Max Values is limited by the rows in all_objects) :5000
old 4: dbms_random.string('X',&ran*dbms_random.value) strval,
new 4: dbms_random.string('X',100*dbms_random.value) strval,
old 5: rpad('x',&pad,'x') padding
new 5: rpad('x',100,'x') padding
old 9: rownum <=&rnums
new 9: rownum <=5000
Table created.
SQL> analyze table TESTING_TABLE compute statistics;
Table analyzed.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
5088
SQL> delete from testing_table where id between &id1 and &id2;
Enter value for id1: 501
Enter value for id2: 1000
old 1: delete from testing_table where id between &id1 and &id2
new 1: delete from testing_table where id between 501 and 1000
500 rows deleted.
SQL> /
Enter value for id1: 2001
Enter value for id2: 2500
old 1: delete from testing_table where id between &id1 and &id2
new 1: delete from testing_table where id between 2001 and 2500
500 rows deleted.
SQL> /
Enter value for id1: 3751
Enter value for id2: 4250
old 1: delete from testing_table where id between &id1 and &id2
new 1: delete from testing_table where id between 3751 and 4250
500 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table TESTING_TABLE compute statistics;
Table analyzed.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
5088
SQL> alter table testing_table enable row movement;
Table altered.
SQL> alter table testing_table shrink space;
Table altered.
SQL> analyze table TESTING_TABLE compute statistics;
Table analyzed.
SQL> select blocks from dba_tables where table_name='TESTING_TABLE';
BLOCKS
----------
5088
SQL> drop table testing_table;
Table dropped
