Segments Shrink

Among Windows users, disk drive fragmentation is a well known terminology. Regular data write and delete operations causes disk drive fragmentation. We usually avoid this by performing disk de-fragmentation.

Now the question is “Does Oracle has same problem ?”
If yes then, what is its remade ?

Unfortunately (i would say naturally :-) ) Oracle also has same problem because a data segment may experienced heavy UPDATE and DELETE. When the segment is rewrite, there is a scope to leave some free space within data blocks, just like disk fragmentation. This free space cause space waist and poor performance because Oracle need to scan more data block to fetch data. Now you should ask “What is the remade?” and the answer is “Segment Shrink”.


Before Oracle 10g

In earlier versions of Oracle, you can reclaim the free segment space by performing the following ways:

1. Create a table same as old table, move data from old table to new table and finally drop the old table.
   
2. Or create the new table as CAST (create table test2 as select * from test). drop test table and rename table test2 to test.
3. You also can move the table to another datafile by using ALTER TABLE MOVE command.

Both of the three processes, cause the table unavailable while performing operations. Another alternative way is 'Online table reorganization'. The benefit is, this time the table is available while performing 'Online table reorganization' but it consume significant disk space.


In Oracle 10g

You can shrink oracle data segments online that means you need not taking tablespaces or objects off line while performing shrinking . Shrinking of a data segment has two phases:

1. Compacted Segment Data: compacting objects. If the object is a table You must enable row movement so that rowid can changed and disable all triggers on that table. It also readjust High-water mark (HWM) to an appropriate location.
   
2. And Shrink Space: Free up unnecessary space.

Lets go to do the job !

ALTER TABLE TEST SHRINK SPACE COMPACT
--This command only compact the table object.

ALTER TABLE TEST SHRINK SPACE
--This command free up unnecessary space from table object.

1. How do you identify which segments should undergo shrinking?
2. How do you make this check process to a regular part of your system maintenance?
   

With Oracle Database 10g, you can easily gather those answer from Oracle Automatic Workload Repository (AWR) infrastructure. You can also use DBMS_ADVISOR expectingly Segment Advisor. You can also use Oracle Enterprise manager to perform the shrinking stuff with some clicks.

So check the cool feature of oracle now !