DBMS_SPACE.SPACE_USAGE #oracle #sql

DBMS_SPACE.SPACE_USAGE.md

set serveroutput on


variable unf number
variable unfb number
variable fs1 number
variable fs1b number
variable fs2 number
variable fs2b number
variable fs3 number
variable fs3b number
variable fs4 number
variable fs4b number
variable full number
variable fullb number
variable partition_name varchar2(30)


--


exec :partition_name := '<PARTITION_NAME>'


begin
dbms_space.space_usage('<OWNER', '<TABLENAME>', 'TABLE PARTITION', :unf, :unfb, :fs1, :fs1b, :fs2, :fs2b, :fs3, :fs3b, :fs4, :fs4b, :full, :fullb, :partition_name);
dbms_output.put_line('Partition Name = '||:partition_name);
dbms_output.put_line('Unformatted Blocks = '||:unfb);
dbms_output.put_line('Blocks with 00-25% free space = '||:fs1b);
dbms_output.put_line('Blocks with 26-50% free space = '||:fs2b);
dbms_output.put_line('Blocks with 51-75% free space = '||:fs3b);
dbms_output.put_line('Blocks with 76-100% free space = '||:fs4b);
dbms_output.put_line('Full Blocks = '||:fullb);
end;
/