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
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,
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.
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 :-