SQL Script
Unused space in extents
|
SQL ScriptUnused space in extents |
|
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
&own - Segment Owner
&tab - Segment name
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;
/
Return to Index of SQL Scripts
Home | Company Profile
| Services | Contact Us
| SQL scripts and tips |
Quiz |
 |