Recovering Space in TableSpace without reorg : Oracle Segment Shrink

[abhijeet_singh2]

Hello All,

Recently while working on a system I ran into a situation where my main tablespace PSAPSR3 had ran into full utilization and all my sapdata's were utilized till 99%. Hence no way to extend my tablespace by adding space or extending TS (a typical quick fixes) or reorgs (long planed fix), as there were no space available.

This was my situation before

While researching I cam across notes

910389 - FAQ: Oracle Segment Shrinking

771929 - FAQ: Index fragmentation

And all my problem got solved in 30 mins itself.

To conclude, with all my experience, Segment shrinking, is indeed a very convenient way of recovering space from tabespace in oracle 10g onwards.

Do try it out yourself.

Below is a step by step procedure,

Prerequisite :-

1. Ensure your tablespace is SSM Auto

2. Table doesn't have LOB columns.

3. prepare a script file like attached. Which i have attached in the place of <table name> put table to be shrinked & in place of <owner> put TS owner. Keep it in any location as its just for monitoring the shrink of table.

Activity :-

1. Execute the script to get current status

    

     And check free space on the table

    

2. Enable row movement.

    

3. Trigger shrink

    

4. Disable Row Movement

    

it took 5 seconds for a 10 MB table & its done.

5. Check status now

6. I receovered 7 MB space for the tablespace.

Closing Point :

Do try it out yourself and comment if I have missed something.

Reference Notes :-

771929 - FAQ: Index fragmentation

910389 - FAQ: Oracle Segment Shrinking

Thanks

Abhijeet