Difference Between Oracle Table Shrink Space, Space Compact, Space Cascade - with Example

advertisements

_____________________________________________________________________________________________________________________

Table shrink is an interesting feature provided by Oracle and this is very useful for the database reorganizations. There are 3 different options available with table shrink command. The table shrinking is achieved by moving the rows between the Oracle blocks. To perform the table shrinking we need to enable the row movement for the table.

Option 1. Alter table table_name shrink space

This option will recover the free space in the table and will update the high water mark. So after performing this activity you can see the change in the number of blocks in the data dictionary tables.

Option 2. Alter table table_name shrink space compact;

This option will recover the free space but it won’t update the high water mark. It is same as the coalesce option. After performing the changes won’t reflect automatically. You will have to perform the shrink space command again to reflect the changes in the dictionary tables.

Option 3. Alter table table_name shrink space cascade;

This option will recover the free space and update the high water mark for the table. Also, it will recover the free space and update the high water mark for all the depended objects like index, etc for the particular table.

Example

//Create example table
SQL> create table objlist as select * from all_objects;

Table created.

//Create index on the table
SQL> create index idx_objlist on objlist(OWNER,OBJECT_NAME);

Index created.

SQL> ANALYZE TABLE OBJLIST COMPUTE STATISTICS;

Table analyzed.
SQL> ANALYZE INDEX idx_objlist COMPUTE STATISTICS;

Index analyzed.
// Taking the initial count for the blocks.
SQL> COL SEGMENT_NAME FOR A30
SQL> SELECT SEGMENT_NAME, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('OBJLIST','IDX_OBJLIST');

SEGMENT_NAME                       BLOCKS

------------------------------ ----------
IDX_OBJLIST                           512
OBJLIST                              1024
//Delete half of the records from the table
SQL> DELETE FROM OBJLIST WHERE ROWNUM<40000 o:p="">

 
39999 rows deleted.
SQL> COMMIT;

Commit complete.

 
SQL> ANALYZE TABLE OBJLIST COMPUTE STATISTICS;
 
Table analyzed.
 
SQL> ANALYZE INDEX IDX_OBJLIST COMPUTE STATISTICS;
 
Index analyzed.
//Still the block count remains the same.
SQL>SELECT SEGMENT_NAME, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN 'OBJLIST','IDX_OBJLIST');

SEGMENT_NAME                       BLOCKS
------------------------------ ----------
IDX_OBJLIST                           512
OBJLIST                              1024
//Enabling the row movement for doing the shrinking activity.
SQL> alter table OBJLIST enable row movement;
 
Table altered.
//Performing the shrink compact which will not make any difference to the block count.
SQL> alter table OBJLIST shrink SPACE COMPACT;
Table altered.

SQL> SELECT SEGMENT_NAME, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('OBJLIST','IDX_OBJLIST');

 
SEGMENT_NAME                       BLOCKS
------------------------------ ----------
IDX_OBJLIST                           512
OBJLIST                              1024
//Performing the shrink space command which will update the high water mark and update the dictionary tables.
SQL>alter table OBJLIST shrink SPACE;
 
Table altered.

SQL> SELECT SEGMENT_NAME, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('OBJLIST','IDX_OBJLIST');

SEGMENT_NAME                       BLOCKS
------------------------------ ----------
IDX_OBJLIST                           512
OBJLIST                               424
//Shrink space cascade option will shrink the table and its Index also. This will update the high water mark after that.
SQL> alter table OBJLIST shrink SPACE CASCADE;
 
Table altered.

SQL> SELECT SEGMENT_NAME, BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('OBJLIST','IDX_OBJLIST');

SEGMENT_NAME                       BLOCKS
------------------------------ ----------
IDX_OBJLIST                           184 // Index blocks changed
OBJLIST                               424

_____________________________________________________________________________________________________________________

Website Stats