Table unsed space in a non ASSM and ASSM tablespace



Last updated on December 21st, 2015 at 06:35 am

The following process may be used at any time to determine the amount of free space within a table in an non ASSM tablespace (Automatic segment space management)

set serveroutput on

declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;

begin
dbms_space.unused_space(
‘table_owner’,
‘table_name’,
‘TABLE’,
TOTAL_BLOCKS,
TOTAL_BYTES,
UNUSED_BLOCKS,
UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID,
LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);

dbms_output.put_line(‘TOTAL_BLOCKS = ‘||TOTAL_BLOCKS);
dbms_output.put_line(‘UNUSED_BLOCKS = ‘||UNUSED_BLOCKS);
end;
/

The following procedure may be used at any time to see the block allocations for a table stored in an ASSM tablespace
set serveroutput on

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 (
‘table_owner’,
‘table_name’,
‘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(‘Blocks with 00-25% free space   = ‘||v_fs1_blocks);
dbms_output.put_line(‘Blocks with 26-50% free space   = ‘||v_fs2_blocks);
dbms_output.put_line(‘Blocks with 51-75% free space   = ‘||v_fs3_blocks);
dbms_output.put_line(‘Blocks with 76-100% free space = ‘||v_fs4_blocks);
dbms_output.put_line(‘Full Blocks                                        = ‘||v_full_blocks);

end;
/

The above procedure was for Table, For table partition , we can use similar queries also

set serveroutput on

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 (
‘table_owner’,
‘table_name’,
‘TABLE PARTITION’,
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,

‘PARTITION_NAME’);

dbms_output.put_line(‘Unformatted Blocks                       = ‘||v_unformatted_blocks);
dbms_output.put_line(‘Blocks with 00-25% free space   = ‘||v_fs1_blocks);
dbms_output.put_line(‘Blocks with 26-50% free space   = ‘||v_fs2_blocks);
dbms_output.put_line(‘Blocks with 51-75% free space   = ‘||v_fs3_blocks);
dbms_output.put_line(‘Blocks with 76-100% free space = ‘||v_fs4_blocks);
dbms_output.put_line(‘Full Blocks                                        = ‘||v_full_blocks);

end;
/


Leave a Reply