Rem Filename : SpaceUtilization.sql Rem Rem Parameters : &1 = [owner.]table(pattern) Rem The default value for owner is the currently connected user. Rem Rem &2 = content indicator : D = detailed information per table Rem S = summary information Rem DS = detailed + summary information Rem Rem Author : Rem RDBMS : Oracle V8.x, v9.x Rem Rem Modification history Rem -------------------- Rem 29-Mar-2002 Initial creation Rem 20-Nov-2003 Completely rewritten Rem - Major performance improvement Rem - Several formatting improvements and additional information. Rem - Added summary (only correct information in case of default blocksize). Rem To do : display correct information in case of non default blocksize. Rem Rem Rem ======================================================================================================================= Rem Rem Description : Lists space occupied by all matching table's data segments and the unused space Rem below and above the high water mark. Rem Rem Table name : The name of the table. Rem Statistics : Shows the date when the table statistics were most recently Rem computed or estimated, and the number of rows in the Rem table at that moment. Rem Segment size : The total number of Kbytes occupied by the segment. Rem Used by data : The total space occupied by real row data (without any overhead). Rem Free list space : The total space free in all free list blocks. Rem Take note that free list blocks might be partially filled. Rem Free above HWM : The space occupied by the blocks in the segment above the free water mark. Rem Overhead + free : The space occupied by : Rem - the segment header Rem - the block headers of all blocks below the high water mark Rem - the free space in blocks containing data but which is not available for Rem further insert because the blocks are not on one of the free lists, Rem in other words for which % occupied by data has not yet dropped below pctused. Rem This space is available for updates in existing rows within the blocks. Rem Rem REMARK : In some cases (outdated statistics) the data listed might look contradictory, this Rem is because some but not all columns are obtained from the table statistics. Rem Rem Sample output Rem ------------- Rem MXXXXXXX @unused crossworlds.% Rem Rem . Statistics Segment Used by Free list Free above Overhead + free Rem Table name Row count (date) Size (Kb) Data (%) Space (Kb) HWM (Kb) above %used (Kb) Rem ----------------------------- ------------------------- ----------- --------- ------------ ----------- ---------------- Rem .. Rem CXREPOSBLOBS 4 (20-NOV-03) 256 31 % 35 88 54 (21%) Rem CXREPOSBMCOMP 0 (20-NOV-03) 128 0 % 0 120 8 (6%) Rem CXREPOSBOSPECATTRS 37 (20-NOV-03) 128 3 % 4 112 8 (6%) Rem CXREPOSBUSOBJREFS 4 (20-NOV-03) 128 0 % 8 112 8 (6%) Rem .. Rem Rem Data segment summary for Rem ======================================== Rem Total space occupied by table data : 90 Kbytes 1,34 % Rem Total storage overhead : 456 Kbytes 6,85 % Rem Total free space for inserts below HWM : 878 Kbytes 13,20 % Rem Total free space above high water marks : 5.232 Kbytes 78,61 % Rem -------------------------------------------------------------------------- Rem Total allocated space (data blocks) : 6.656 Kbytes 100,00 % Rem Rem ======================================================================================================================= SET PAGESIZE 0 SET TRIMSPOOL ON SET VERIFY OFF SET SERVEROUTPUT ON SIZE 500000 FORMAT WORD_WRAPPED SET FEEDBACK OFF alter session set nls_numeric_characters = ',.'; -- Disect the input argument, and get the owner name and table name into two seperate -- substitution variables. The owner name defaults to the current user. DEFINE InputOwner = ' ' DEFINE InputTableName = ' ' DEFINE DefaultStorageEfficiency = 80 COLUMN table_owner NOPRINT NEW_VALUE InputOwner COLUMN table_name NOPRINT NEW_VALUE InputTableNameFilter SELECT decode(instr('&&1','.'), 0, user, /*Default to current user.*/ upper(substr('&&1',1,instr('&&1','.')-1))) table_owner, decode(instr('&&1','.'), 0, upper('&&1'), /*Only the table name was passed in.*/ upper(substr('&&1',instr('&&1','.')+1))) table_name FROM dual; CLEAR COLUMNS declare total_blocks number; total_bytes number; unused_blocks number; unused_bytes number; last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; FreeBelowHWM number := 0; TotalFreeListSpace number := 0; TotalUnusedBlocks number := 0; TotalSize number := 0; TotalDataBytes number := 0; OccupiedByOthers number := 0; WastedSpace_Kbytes number := 0; deleted_index_rows number := 0; TotalIndexSize number := 0; v_block_size number; v_block_size_Kbytes number; no_rows boolean := TRUE; SummaryTitle varchar2(100); E_segment_does_not_exist EXCEPTION; PRAGMA EXCEPTION_INIT(E_segment_does_not_exist, -3211); E_no_privilege EXCEPTION; PRAGMA EXCEPTION_INIT(E_no_privilege, -1031); E_no_access EXCEPTION; PRAGMA EXCEPTION_INIT(E_no_access, -942 ); cursor index_info (i_table_name VARCHAR2, i_table_owner VARCHAR2) is SELECT i.owner, i.index_name, i.num_rows, s.bytes/1024 size_Kbytes FROM dba_indexes i, dba_segments s WHERE i.table_name = i_table_name AND i.table_owner = i_table_owner AND i.index_name = s.segment_name AND i.owner = s.owner; begin SELECT value,value/1024 INTO v_block_size,v_block_size_Kbytes FROM v$parameter WHERE name = 'db_block_size'; for t in ( SELECT t.owner, t.table_name, t.iot_type, decode(t.last_analyzed,null,' Never analyzed', to_char(t.num_rows,'999G999G999') || ' (' || to_char(t.last_analyzed,'DD-MON-YY') || ')' ) RowCountStats, decode(t.last_analyzed,null,' =?=', to_char(t.AVG_SPACE_FREELIST_BLOCKS*t.num_freelist_blocks/1024,'999G999G999')) FreeListSpace, nvl(t.AVG_SPACE_FREELIST_BLOCKS*t.num_freelist_blocks,0) FreeListBytes, decode(t.last_analyzed,null,' =?=', ' ' || to_char(100*t.num_rows*t.avg_row_len/s.bytes,'99')||' %') RealData, -- Let's use -1 for "no statistics available" nvl(t.num_rows*t.avg_row_len,-1) RealDataBytes, s.blocks, t.empty_blocks FROM dba_tables t, dba_segments s WHERE t.owner = '&InputOwner' AND t.table_name like '&InputTableNameFilter' -- exclude temporary tables but include index organized tables AND ( s.tablespace_name is not null OR t.iot_type = 'IOT' ) AND t.owner = s.owner AND t.table_name = s.segment_name ORDER BY t.owner, t.table_name ) loop if no_rows then if ( instr( '&2', 'D' ) > 0 ) then dbms_output.put_line( '. Statistics Segment Used by Free list Free above Overhead + free' ); dbms_output.put_line( 'Table name Row count (date) Size (Kb) Data (%) Space (Kb) HWM (Kb) above %used (Kb)' ); dbms_output.put_line( '----------------------------- ------------------------- ----------- --------- ------------ ----------- ----------------' ); end if; no_rows := FALSE; end if; -- --------------------------------------------------------------------------------------------------------- -- Collect information about the data segment -- --------------------------------------------------------------------------------------------------------- -- Some information can be retreived by the next call that is not available in dba_tables unless the table was -- analyzed. The next call is causing nearly no system load compared with using dbms_stats. begin begin dbms_space.unused_space( t.owner, t.table_name, 'TABLE', total_blocks, total_bytes, unused_blocks, unused_bytes, last_used_extent_file_id, last_used_extent_block_id, last_used_block -- The next paramter is optional, I put it in for future support of partitioned tables. -- , partition_name ); exception when E_no_privilege or E_no_access then -- An ORA-01031 is raised when the user is not allowed to execute the dbms_space package. -- An ORA-00942 is raised by dbms_space.unused_space when the user has no access to the table. -- Get the data from the table statistics (if these are available). unused_blocks := t.empty_blocks; total_blocks := t.blocks; when others then dbms_output.put_line( 'The following error was trapped while processing table <' || t.table_name || '>.' ); raise; end; TotalFreeListSpace := TotalFreeListSpace + t.FreeListBytes; TotalUnusedBlocks := TotalUnusedBlocks + nvl(unused_blocks,0); TotalSize := TotalSize + total_blocks*v_block_size_Kbytes; if t.RealDataBytes = -1 then -- If no table statistics, then we only can estimate the space occupied by the real data. -- Without this estimation the total summaries rappidly become meaningless. TotalDataBytes := TotalDataBytes + round((total_bytes-unused_bytes)*(&DefaultStorageEfficiency/100)); else TotalDataBytes := TotalDataBytes + t.RealDataBytes; end if; WastedSpace_Kbytes := total_blocks*v_block_size_Kbytes - round(t.RealDataBytes/1024 + t.FreeListBytes/1024 + unused_blocks*v_block_size_Kbytes ); if ( instr( '&2', 'D' ) > 0 ) then dbms_output.put_line( rpad(t.table_name,31) || t.RowCountStats || ' ' || to_char(total_blocks*v_block_size_Kbytes,'99G999G999') || ' ' || t.RealData || ' ' || t.FreeListSpace || ' ' || to_char(unused_blocks*v_block_size_Kbytes,'99G999G999') || ' ' || to_char(WastedSpace_Kbytes, '9G999G999') || ' (' || round(WastedSpace_Kbytes*100/(total_blocks*v_block_size_Kbytes)) || '%)' ); end if; exception when E_segment_does_not_exist then -- This is an index organized table, so there is no regular data segment. dbms_output.put_line( rpad(t.table_name,31) || t.RowCountStats || ' =iot=' ); when others then raise; end; /* -- --------------------------------------------------------------------------------------------------------- -- Collect information about the index segments -- --------------------------------------------------------------------------------------------------------- for i in index_info( t.table_name, t.owner ) loop execute immediate 'validate index ' || i.owner || '.' || i.index_name; SELECT height, del_lf_rows, lf_rows, del_lf_rows/(lf_rows+0.00001), blocks*db.block_size, round(btree_space/1024), nvl(pct_used,0) INTO b_tree_height, deleted_rows, index_rows, degradation, index_size, btree_space, btree_pct_used FROM index_stats, (SELECT value/1024 block_size FROM v$parameter WHERE name = 'db_block_size') db; SELECT del_lf_rows INTO deleted_index_rows FROM index_stats; dbms_output.put_line( rpad('. ' || i.index_name,31) || rpad(to_char(i.num_rows+deleted_index_rows,'999G999G999'),25) || to_char(i.size_Kbytes,'99G999G999') ); end loop; */ end loop; SELECT sum(bytes)/1024 INTO TotalIndexSize FROM dba_indexes i, dba_segments s WHERE i.table_owner = '&InputOwner' AND i.table_name like '&InputTableNameFilter' AND i.index_name = s.segment_name AND i.owner = s.owner; if no_rows then dbms_output.put_line( 'No matching tables found.' ); else if ( instr( '&2', 'S' ) > 0 ) then OccupiedByOthers := TotalSize - ( (TotalDataBytes/1024) + (TotalFreeListSpace/1024) + (TotalUnusedBlocks*v_block_size_Kbytes) ); SummaryTitle := 'Data segment summary for <&1>'; dbms_output.put_line( chr(10) || SummaryTitle || chr(10) || rpad('=',length(SummaryTitle),'=') ); dbms_output.put_line( 'Total space occupied by table data : ' || to_char(TotalDataBytes/1024,'9G999G999') || ' Kbytes ' || to_char((TotalDataBytes/1024)*(100/TotalSize),'990D99') || ' %' ); dbms_output.put_line( 'Total storage overhead : ' || to_char(OccupiedByOthers,'9G999G999') || ' Kbytes ' || to_char(OccupiedByOthers*(100/TotalSize),'990D99') || ' %' ); dbms_output.put_line( 'Total free space for inserts below HWM : ' || to_char(TotalFreeListSpace/1024,'9G999G999') || ' Kbytes ' || to_char((TotalFreeListSpace/1024)*(100/TotalSize),'990D99') || ' %' ); dbms_output.put_line( 'Total free space above high water marks : ' || to_char(TotalUnusedBlocks*v_block_size_Kbytes,'9G999G999') || ' Kbytes ' || to_char(TotalUnusedBlocks*v_block_size_Kbytes*100/TotalSize,'990D99') || ' %' ); dbms_output.put_line( '--------------------------------------------------------------------------' ); dbms_output.put_line( 'Total allocated space (data segments : ' || to_char(TotalSize,'9G999G999') || ' Kbytes 100,00 %' ); dbms_output.put_line( 'Total allocated space (index segments) : ' || to_char(TotalIndexSize,'9G999G999') || ' Kbytes' ); end if; end if; end; / PROMPT SET FEEDBACK ON SET PAGESIZE 9999 UNDEFINE InputOwner UNDEFINE InputTableName UNDEFINE DefaultStorageEfficiency