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.

HWM - High Water Mark

What is High Water Mark(HWM)?

HWM is boundry between used block and unused block. Blocks below HWM is used blocks and above HWM is unused blocks. Used blocks might or might not have the data. In normal operations (Insert/update), the HWM is mostly bump up and never go down. Oracle tracks the HWM for the segment in segment header.

How do we identify the HWM?

Option 1

We can use DBMS_SPACE.UNUSED_SPACE procedure.

Please remember, i used pipe symbol in the below code and it is not visible in blog. So placed ### symbol in the place of pipe sign. Please replace thie ### sign with pipe symbol when we run this code in any of your test database.

DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM user_tables where table_name='COLLECTION') LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => USER,
segment_name => c1_row.table_name ,
segment_type => 'TABLE' ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( 'Data for '### c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD('*',LENGTH(c1_row.table_name) + 10,'*'));
DBMS_OUTPUT.PUT_LINE( 'Total Blocks.................'### alc_bks );
DBMS_OUTPUT.PUT_LINE( 'Total Bytes..................'###alc_bts );
DBMS_OUTPUT.PUT_LINE( 'Unused Blocks................'### unsd_bks );
DBMS_OUTPUT.PUT_LINE( 'Unused Bytes.................'###unsd_bts );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext File Id........'### luefi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext Block Id.......'### luebi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Block..............'###lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

Here is the output for the above code.

Data for COLLECTION
************************
Total Blocks.................38656
Total Bytes..................158334976
Unused Blocks................0
Unused Bytes.................0
Last Used Ext File Id........60
Last Used Ext Block Id.......1284368
Last Used Block..............256

PL/SQL procedure successfully completed.

SQL>
HWM = Total Blocks - Unused Blocks
HWM = 38656-0
HWM = 38656

Option 2

we can use user_tables.
select sum(blocks)-sum(empty_blocks) from user_tables

Please remember, the above query gives the result based on the current statistics. Best way to find the HWM is to use DBMS_SPACE.UNUSED_SPACE(Option 1) procedure.

How do we find which table is good candidate to reset the HWM?

I have answered for this as a different topic in the same blog. Please click to answer this question.

Why do we need to reset the HWM?

1. When ever optimizer takes full table scan, it scan all the blocks below HWM. This would degrade the performance and slowdown the Full table scan process. To avoid this, we need to shrink the table to reset the HWM.

2. Oracle does not release the free space under HWM for use by other objects for a simple reason: because that space is reserved for new inserts and to accommodate the growth of existing rows. The additional benefit of resetting HWM is a more efficient utilization of space because the space freed is available for other objects.

3. When rows are inserted with direct path—for example, through Direct Load Insert (insert with the APPEND hint) or through the SQL*Loader direct path—the data blocks are placed directly above the HWM. The space below it remains wasted.

How do we reset the HWM?

Option 1
Alter table emp move tablespace

This option requires rebuilding the indexes. since the index will be invalid/unusable after running the above command. The downside is, rebuilding the index is additional overhead when we use this option. Also users can not use the application or reports while rebuilding the index.

Option 2

1. Export the data
2. truncate the table
3. import the table
4. Analyze the table

Option 3
1. copy the table data
2. truncate the original table
3. insert back.

Option 4
Use DBMS_REDEFINITION package to copy the table.

Option5
In oracle10g, we have better option to reset the high water mark. We need to enable row movement before we shrink the space. We do not need to rebuild the index after reseting the HWM. This is the best option to reset the HWM. But this feature is not available in oracle9i.

SQL@DBA1> alter table bookings enable row movement;

Table altered.

SQL@DBA1>
SQL@DBA1> alter table bookings shrink space compact;

Table altered.

If you need to read more about how to reset the HWM in oracle10g, please click this link. I wrote an article as a different topics.