set pagesize 300 set linesize 250 set verify off define lnum = "format 9G999G999D99" define num = "format 99G999" define snum = "format 9G999" ttitle left "Space Usage of a table" skip 2 define ENTER_OWNER='&TABLE_OWNER' define ENTER_TABLE='&TABLE_NAME' column segment_name format A20 column owner format A10 column Size_MB &&lnum column count_blk &&num column count_ext &&snum column count_part &&snum select segment_name ,owner ,ROUND (SUM (bytes) / 1024 / 1024, 3) as Size_MB ,SUM (blocks) as count_blk ,SUM (EXTENTS) as count_ext ,COUNT (*) as count_part from dba_segments where UPPER (segment_name) like UPPER ('&ENTER_TABLE.%') and UPPER (owner) = UPPER ('&ENTER_OWNER.') group by segment_name, owner / /* Parameter Description ----------------------------------------------------------------------- segment_owner Schema name of the segment to be analyzed segment_name Name of the segment to be analyzed partition_name Partition name of the segment to be analyzed segment_type Type of the segment to be analyzed (TABLE, INDEX, or CLUSTER) unformatted_blocks Total number of blocks that are unformatted unformatted bytes Total number of bytes that are unformatted fs1_blocks Number of blocks that has at least 0 to 25% free space fs1_bytes Number of bytes that has at least 0 to 25% free space fs2_blocks Number of blocks that has at least 25 to 50% free space fs2_bytes Number of bytes that has at least 25 to 50% free space fs3_blocks Number of blocks that has at least 50 to 75% free space fs3_bytes Number of bytes that has at least 50 to 75% free space fs4_blocks Number of blocks that has at least 75 to 100% free space fs4_bytes Number of bytes that has at least 75 to 100% free space ful1_blocks Total number of blocks that are full in the segment full_bytes Total number of bytes that are full in the segment ---------------------------- total_blocks Returns total number of blocks in the segment. total_bytes Returns total number of blocks in the segment, in bytes. unused_blocks Returns number of blocks which are not used. unused_bytes Returns, in bytes, number of blocks which are not used. last_used_extent_ file_id Returns the file ID of the last extent which contains data. last_used_extent_ block_id Returns the starting block ID of the last extent which contains data. last_used_block Returns the last block within this extent which contains data. */ set serveroutput on /* Formatted on 24.7.2013. 21:49:56 (QP5 v5.185.11230.41888) */ 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; total_blocks number; total_bytes number; unused_blocks number; unused_bytes number; lastextf number; last_extb number; lastusedblock number; v_file_name dba_data_files.file_name%type; v_tablespace_name dba_segments.TABLESPACE_NAME%type; v_segment_management dba_tablespaces.SEGMENT_SPACE_MANAGEMENT%type; cursor c_seg is select segment_name, owner, SEGMENT_TYPE from dba_segments where UPPER (segment_name) like UPPER ('&ENTER_TABLE.%') and UPPER (owner) = UPPER ('&ENTER_OWNER.'); begin for rec in c_seg loop begin DBMS_OUTPUT.put_line ( 'Info -- Call dbms_space.space_usage for table ( Type:' || rec.segment_type || ' ) ::' || rec.segment_name); DBMS_OUTPUT.put_line ( 'Info ------------------------------------------------------------------'); DBMS_SPACE.space_usage (rec.owner ,rec.segment_name ,rec.segment_type ,unf ,unfb ,fs1 ,fs1b ,fs2 ,fs2b ,fs3 ,fs3b ,fs4 ,fs4b ,full ,fullb); DBMS_OUTPUT.put_line ( 'Info -- Total Count of blocks that are unformatted :' || unf || ' Bytes :' || unfb); DBMS_OUTPUT.put_line ( 'Info -- Total Count of blocks that are full in the segment :' || full || ' Bytes :' || fullb); DBMS_OUTPUT.put_line ('Info -- '); DBMS_OUTPUT.put_line ( 'Info -- Count of blocks that has at least 0 to 25% free space :' || fs1 || ' Bytes :' || fs1b); DBMS_OUTPUT.put_line ( 'Info -- Count of blocks that has at least 25 to 50% free space :' || fs2 || ' Bytes :' || fs2b); DBMS_OUTPUT.put_line ( 'Info -- Count of blocks that has at least 50 to 75% free space :' || fs3 || ' Bytes :' || fs3b); DBMS_OUTPUT.put_line ( 'Info -- Count of blocks that has at least 75 to 100% free space :' || fs4 || ' Bytes :' || fs4b); DBMS_OUTPUT.put_line ( 'Info ------------------------------------------------------------------'); exception when others then DBMS_OUTPUT.put_line ('Error --'); DBMS_OUTPUT.put_line ('Error -- ' || SQLERRM); DBMS_OUTPUT.put_line ( 'Error -- +This procedure can be used only on segments in tablespaces with AUTO SEGMENT SPACE MANAGEMENT'); DBMS_OUTPUT.put_line ( 'Error -- +Action: Recheck the segment name and type and re-issue the statement'); select s.TABLESPACE_NAME, t.SEGMENT_SPACE_MANAGEMENT into v_tablespace_name, v_segment_management from dba_segments s, dba_tablespaces t where UPPER (s.segment_name) like UPPER ('&ENTER_TABLE.%') and UPPER (s.owner) = UPPER ('&ENTER_OWNER.') and s.TABLESPACE_NAME = t.TABLESPACE_NAME; DBMS_OUTPUT.put_line ( 'Error -- +Tablespace for the table &ENTER_TABLE.:: ' || v_tablespace_name || ' - Segment Management for this tablespace:: ' || v_segment_management); DBMS_OUTPUT.put_line ('Error --'); end; begin DBMS_OUTPUT.put_line ( 'Info -- Call dbms_space.UNUSED_SPACE for table ( Type:' || rec.segment_type || ' ) ::' || rec.segment_name); DBMS_SPACE.UNUSED_SPACE (rec.owner ,rec.segment_name ,rec.segment_type ,total_blocks ,total_bytes ,unused_blocks ,unused_bytes ,lastextf ,last_extb ,lastusedblock); DBMS_OUTPUT.put_line ( 'Info ------------------------------------------------------------------'); DBMS_OUTPUT.put_line ( 'Info -- Used total_blocks :' || total_blocks); DBMS_OUTPUT.put_line ( 'Info -- Used total_bytes :' || total_bytes); DBMS_OUTPUT.put_line ( 'Info -- Unused block :' || unused_blocks); DBMS_OUTPUT.put_line ( 'Info -- Unused byte :' || unused_bytes); DBMS_OUTPUT.put_line ( 'Info -- File ID of the last extent with data :' || lastextf); select file_name into v_file_name from dba_data_files where FILE_ID = lastextf; DBMS_OUTPUT.put_line ( 'Info -- File Name last extent with data :' || v_file_name); DBMS_OUTPUT.put_line ( 'Info -- Starting block ID of the last extent :' || last_extb); DBMS_OUTPUT.put_line ( 'Info -- Last block within this extent :' || lastusedblock); DBMS_OUTPUT.put_line ( 'Info ------------------------------------------------------------------'); exception when others then DBMS_OUTPUT.put_line ('Error ---' || SQLERRM); end; end loop; end; / set verify on ttitle off