SQL Script Space Usage

Updated : 14-Mar-2002
Version : 1.0

Description

This procedure used a new procedure in Oracle 9i which displays a breakdown of the block usage within a given segment.

Parameters

&own - Segment Owner
&tab - Segment name

SQL Source

set serveroutput on size 1000000
set verify off

spool spaceu.lst

ACCEPT own PROMPT 'Enter value for owner (RETURN for ALL) > '
ACCEPT tab PROMPT 'Enter value for SEGMENT name (RETURN for ALL) > '

set doc off
DECLARE	
   unf number; 
   unfb number; 
   fs1 number; 
   fs1b number; 
   fs2 number; 
   fs2b number; 
   fs3 number; 
   fs3b number; 
   fs4 number; 
   fs4b number; 
   full number; 
   fullb number; 

   CURSOR get_segment IS SELECT * 
                         FROM dba_segments
                         WHERE owner like UPPER('%&own%')
                         AND owner NOT IN ('SYS','SYSTEM')
                         AND segment_name like UPPER('%&tab%')
                         ORDER BY OWNER, segment_type, segment_name;

BEGIN

FOR seg_rec IN get_segment LOOP

   dbms_space.space_usage(segment_owner => seg_rec.owner		
                , segment_name => seg_rec.segment_name
		, segment_type => seg_rec.segment_type	
                , unformatted_blocks => unf
                , unformatted_bytes => unfb
                , fs1_blocks => fs1
                , fs1_bytes => fs1b
                , fs2_blocks => fs2
                , fs2_bytes => fs2b
                , fs3_blocks => fs3
                , fs3_bytes => fs3b
                , fs4_blocks => fs4
                , fs4_bytes => fs4b
                , full_blocks => full
                , full_bytes => fullb
                , partition_name=>  seg_rec.partition_name );

dbms_output.put_line ('**************************************************************************');
dbms_output.put_line (seg_rec.owner		|| ' ' ||seg_rec.segment_type
		|| ' ' ||seg_rec.segment_name );
dbms_output.put_line ('-------------------------------------------------------------------------');
dbms_output.put_line('Tablespace : '||seg_rec.tablespace_name);
dbms_output.put_line('Total Unformatted Bytes : '||unfb|| ' ('||unf||' blocks)');
dbms_output.put_line('Total  0-25% Free : '||fs1b|| ' ('||fs1||' blocks)');
dbms_output.put_line('Total  25-50% Free : '||fs2b|| ' ('||fs2||' blocks)');
dbms_output.put_line('Total  50-75% Free : '||fs3b|| ' ('||fs3||' blocks)');
dbms_output.put_line('Total  75-100% Free : '||fs4b|| ' ('||fs4||' blocks)');
dbms_output.put_line('Total Full : '||fullb|| ' ('||full||' blocks)');

END LOOP;

END;
/

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal