This article is tested in oracle 22.214.171.124.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.
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);
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.
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.