SQL Script

Unused space in extents

Updated : 1-October-2000
Version : 1.1

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 <index name> REBUILD command

Parameters

&own - Segment Owner
&tab - Segment name

SQL Source

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%TYPE;
	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(seg_rec.owner
		, seg_rec.segment_name
		, seg_rec.segment_type
		, tot_blocks
		, tot_bytes
		, unused_blocks
		, unused_bytes
		, last_ext_file_id
		, last_ext_block_id
		, last_block );

	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;
/