How do we reclaim the wasted space in a segment?

This article is tested in oracle 9.2.0.6.0. Before reclaiming the wasted space, First, we need to find out exactly how much space is wasted in that segment that can be reclaimed. To find out the wasted space in the segment, we can use SPACE_USAGE procedure in DBMS_SPACE pacakge. To use this procedure, the tablespace should be ASSM enabled.

I have used pipe symbol in the PLSQL procedure. But it is not visible in the blog. So i used ### symbol where ever, there is the pipe sign. So please replace the ### with pipe symbol in the code when you want to run this script in your test database.

DECLARE
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
BEGIN
dbms_space.space_usage ('INVENTADMIN',
'COLLECTION',
'TABLE',
v_unformatted_blocks,
v_unformatted_bytes,
v_fs1_blocks,
v_fs1_bytes,
v_fs2_blocks,
v_fs2_bytes,
v_fs3_blocks,
v_fs3_bytes,
v_fs4_blocks,
v_fs4_bytes,
v_full_blocks,
v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '###v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '###v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '###v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '###v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '###v_fs4_blocks);
dbms_output.put_line('Full Blocks = '###v_full_blocks);
end;
/

Here is the output for the above code.

Unformatted Blocks = 0
FS1 Blocks = 32
FS2 Blocks = 60
FS3 Blocks = 63
FS4 Blocks = 19671
Full Blocks = 5

PL/SQL procedure successfully completed.
SQL>
As per the above output
32 blocks has 0-25% free.
60 blocks has 25-50% free.
63 blocks has 50-75% free.
19671 blocks has 50-75% free.
5 blocks has 100% free

Now we have lot of blocks 50-75% free. so we can reclaim the space. We can reset the HWM to reorganize the space in the segment. Resetting the HWM is discussed in another topic in the same blog. Please click to read how to reset the HWM.