SQL Script Unused Space in extents

Updated: 14-Mar-2002
Version: 2.0

Description

This scripts displays details about how must unused space there is in segments in objects. 

This script is useful in determining if a table/index is about to extend, or to allow a decision to be make as to whether to reclaim space from a table or index. 

This could be useful for example where a pctincrease has been set on a table and it's current extent has grown eccessively. The space is deallocated by the command :

ALTER < object > < object_name > DEALLOCATE UNUSED KEEP < number of Kbytes to keep >

however indexes may be better served by ALTER INDEX  REBUILD command

Parameters

own - Segment Owner
tab - Segment name

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set serveroutput on size 1000000
set verify off

spool unused.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	
   tot_blocks number;
   tot_bytes number;
   unused_bytes number;
   unused_blocks number;
   last_ext_file_id number;	
   last_ext_block_id number;
   last_block number;

   l_dba_ext DBA_EXTENTS%ROWTYPE;
   l_file_name DBA_DATA_FILES.file_name%TYPE;

   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.unused_space(segment_owner => seg_rec.owner		
                , segment_name => seg_rec.segment_name
		, segment_type => seg_rec.segment_type	
                , total_blocks => tot_blocks
		, total_bytes => tot_bytes		
                , unused_blocks => unused_blocks
		, unused_bytes => unused_bytes
         	, last_used_extent_file_id => last_ext_file_id
         	, last_used_extent_block_id => last_ext_block_id
         	, last_used_block => last_block  
                , partition_name=>  seg_rec.partition_name );

   SELECT * INTO l_dba_ext	
   FROM dba_extents WHERE file_id = last_ext_file_id
   AND block_id = last_ext_block_id;

   SELECT file_name INTO l_file_name
   FROM dba_data_files	
   WHERE file_id = last_ext_file_id;

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 : '||l_dba_ext.tablespace_name);
dbms_output.put_line('Filename : '||l_file_name);
dbms_output.put_line('Total Bytes : '||tot_bytes
		|| ' ('||tot_blocks||' blocks)');
dbms_output.put_line('Unused Bytes : '||unused_bytes
		|| ' ('||unused_blocks||' blocks)');
dbms_output.put_line('High Water Mark : '||TO_CHAR(tot_bytes-unused_bytes));
dbms_output.put_line('Percentage used: '
		||TO_CHAR(ROUND((1-unused_bytes/tot_bytes)*100,1) )		||' % ');
-- dbms_output.put_line('Last Used Extent File ID : '||l_file_name);
-- dbms_output.put_line('Last Used Extent Block ID :		'||last_ext_block_id);
dbms_output.put_line('Last Used Block : '||last_block);
dbms_output.put_line('Current Extent Size : '||l_dba_ext.bytes	
		|| ' ('||l_dba_ext.blocks||' blocks)');
dbms_output.put_line('Percentage of Extent Used : '
        	||TO_CHAR( ROUND((last_block/l_dba_ext.bytes)*100,1) )	||' % ');

END LOOP;

END;
/

Related Links

Oracle 8: (SQL Script) Unused Space in extents

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links