Archive for June 11th, 2007

Oracle 10g introduced a new feature of shrinking database segments online to reclaim the unused space below the high water mark (level at which blocks were used at some point. We can consider it as a point between used and un-used space within a segment). This is a very cool feature, considering the options we had prior to 10g to reclaim the space back either by dropping and re-creating the table, by using exp/imp or by moving table to different tablespace using ‘ALTER TABLE ..MOVE’ command. All these operations are offline operations.

On the other hand, shrinking segments is an online operation. DML activities like UPDATE and DELETE can cause some free space in the segment which cannot be reused for new data, if it cannot fit into this space. In such cases data is put into new blocks and HWM is adjusted accordingly. Over a period of time, such pockets of fragmented space results into more wasted space and performance issues. Shrinking segment process comes to rescue at this point. We can perform DML operation when command is being executed and hence no down time.

Shrinking segments works in two parts. First it compact the segment by consolidating free space and second it readjust the HWM to de-allocate the space. One thing to remember is, shrinking segments are available only for segments in locally managed tablespaces with automatic segment space management (ASSM). Think of it as if Oracle is reading the table from the bottom and going up while reading it…the moment it hits the first row (the last row in the table), it would take it and re-insert it as close to the top as possible. When it runs out of space at the top, it will then stop since all the free space is now at the bottom of the table. It can then re-draw the HWM and thus release the allocated space.

Online segment shrinking does not work on
• Tables with function based indexes.
• Tables with ROWID based materialized views.
• IOT mapping tables.

Let’s consider an example to illustrate this feature. First step to check is whether ASSM is available for the tablespace or not. Connect to SQL*Plus and issue the following statement.

Let us delete some data to create free space which we can reclaim by shrinking segment online.

SQL> DELETE FROM TEST WHERE ROWNUM <= 20000;

20000 rows deleted.

SQL> COMMIT;

Execute the same query shown above to check the space and it will return exact same results. Process of shrinking segments, physically move rows around so, we need to enable the row movement for the table.

SQL> ALTER TABLE TEST ENABLE ROW MOVEMENT;

Table altered.

Now let us issue the command to shrink the segment.

SQL> ALTER TABLE TEST SHRINK SPACE CASCADE;

Table altered.

Let us re-visit the user_segment table to check for space utilization. Issue above mentioned query and you will see that number on BYTES and BLOCKS are reduced.

Here we have used CASCADE clause with SHRINK SPACE command to shrink space from dependent objects as well. Segment space is compacted, HWM is re-set and unused space is return to the database. If CASCADE option is omitted, it will not shrink the space for dependent objects. We need to issue this command separately on dependent objects.

There is another clause COMPACT which can be used with SHRINK SPACE command. COMPACT clause shrink the space and compact the segment but it does not reset the HWM and delays it to the later time. An exclusive lock is acquired on the segment when HWM is reset for a very small period of time. So if application has long running queries, this option is particularly useful. In order to reset the HWM later, SHRINK SPACE command need to be issued again without COMPACT clause.