Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 19 June 2012

Re-claimed Wasted space in Oracle 10g

We can shrink segments, tables, and indexes to reclaim free blocks and give them to the database for other uses, provided that Automatic Segment Space Management (ASSM) is enabled in your tablespace. I found very good explanation
1. To find out exactly how much space is wasted in that segment that can be re-claimed use the procedure SPACE_USAGE of the package DBMS_SPACE
NOTE: About DBMS_OUTPUT.PUT_LINE. If you are not getting any output in TOAD (Some times behavior is unchecked) then Click on View -> Toad Options -> Editor -> Excute/Compile and check poll for DBMS Output when detected.
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
dbms_space.space_usage(segment_owner => 'HR', segment_name => 'test', segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||'Bytes = '||l_full_bytes);
The output is:

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 4148 Bytes = 0
Full Blocks = 2 Bytes = 16384

The output shows that there are 4,148 blocks with 75-100% free space (FS4); no other free blocks are available. There are only 2 full blocks. The 4,148 blocks can be recovered.

2. Ensure that the table is row-movement enabled. If it’s not, you can enable it with:
Alter table employees enable row movement;

or via Enterprise Manager (on the Administration page). You should also ensure that all rowid-based triggers are disabled on this table because the rows are moved and the rowids could change.

3. Reorganize the existing rows of the table with:
Alter table test shrink space compact;

This command re-distributes the rows inside the blocks, resulting in more free blocks under the HWM—but the HWM itself is not disturbed.

4. Select blocks from user_segments where segment_name = 'TEST';

The number of blocks occupied by the table—4,224—remains the same because the HWM has not moved from its original position. Now move the HWM to a lower position and reclaim the space with
Alter table test shrink space;

Note that the clause COMPACT is not present. This operation will return the unused blocks to the database and reset the HWM. Test it by checking the space allocated to the table:

Select blocks from user_segments where segment_name = 'TEST';

The number of blocks is down from 4,224 to 8; all the unused space inside the table was returned to the tablespace for use in other segments. This shrink operation occurs completely online and does not affect users. You can also compact the indexes of the table in one statement:
Alter table test shrink space cascade;


  1. Hi guys,
    Here is a good explanation how to reclaim the wasted space in a segment