Monday, October 13, 2008

DBMS_SPACE.SPACE_USAGE

# For ASSM tablespaces, check space usage :-

create or replace procedure show_space (a_seg_owner IN VARCHAR2, a_seg_name IN VARCHAR2, a_seg_type IN VARCHAR2) is
unformatted_blocks NUMBER;
unformatted_bytes NUMBER;
fs1_blocks NUMBER;
fs1_bytes NUMBER;
fs2_blocks NUMBER;
fs2_bytes NUMBER;
fs3_blocks NUMBER;
fs3_bytes NUMBER;
fs4_blocks NUMBER;
fs4_bytes NUMBER;
full_blocks NUMBER;
full_bytes NUMBER;
BEGIN
DBMS_SPACE.SPACE_USAGE(
segment_owner => a_seg_owner,
segment_name => a_seg_name,
segment_type => a_seg_type,
unformatted_blocks => unformatted_blocks,
unformatted_bytes => unformatted_bytes,
fs1_blocks => fs1_blocks,
fs1_bytes => fs1_bytes,
fs2_blocks => fs2_blocks,
fs2_bytes => fs2_bytes,
fs3_blocks => fs3_blocks,
fs3_bytes => fs3_bytes,
fs4_blocks => fs4_blocks,
fs4_bytes => fs4_bytes,
full_blocks => full_blocks,
full_bytes => full_bytes,
partition_name=>NULL);

dbms_output.put_line('Space usage report for ' || a_seg_type || ': ' ||
a_seg_owner || '.' || a_seg_name);
dbms_output.put_line('***************************************');
dbms_output.put_line('Unformatted : '||unformatted_blocks||' blocks, '||
unformatted_bytes/1024/1024||' MB');
dbms_output.put_line(' 0% < Free Space < 25% : '||fs1_blocks||' blocks, '||
fs1_bytes/1024/1024||' MB');
dbms_output.put_line('25% < Free Space < 50% : '||fs2_blocks||' blocks, '||
fs2_bytes/1024/1024||' MB');
dbms_output.put_line('50% < Free Space < 75% : '||fs3_blocks||' blocks, '||
fs3_bytes/1024/1024||' MB');
dbms_output.put_line('75% < Free Space < 100% : '||fs4_blocks||' blocks, '||
fs4_bytes/1024/1024||' MB');
dbms_output.put_line('Full blocks : '||full_blocks||' blocks, '||
full_bytes/1024/1024||' MB');
END;
/

SQL> set serveroutput on
SQL> exec show_space ('ETSDYN','ETPPRICE','TABLE');
Space usage report for TABLE: ETSDYN.ETPPRICE
***************************************
Unformatted : 0 blocks, 0 MB
0% < Free Space < 25% : 16 blocks, .125 MB
25% < Free Space < 50% : 3 blocks, .0234375 MB
50% < Free Space < 75% : 4 blocks, .03125 MB
75% < Free Space < 100% : 2004570 blocks, 15660.703125 MB
Full blocks : 0 blocks, 0 MB

PL/SQL procedure successfully completed.