Rem Filename : tsSpace.sql Rem Rem Parameters : &1 = Tablespace_name Rem Rem Author : Rem RDBMS : Oracle V8.x, v9.x Rem Rem Rem Sample output 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 term ON SPOOL &&ruta_fichero set term OFF @@header_dbinfo 107 DC SET LINESIZE 3500 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 DefaultStorageEfficiency = 80 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; TSTotalSize 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 ); begin SELECT value,value/1024 INTO v_block_size,v_block_size_Kbytes FROM v$parameter WHERE name = 'db_block_size'; /*for ts in (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('CWMLITE','DRSYS','INDX','ODM','SYSTEM','TEMP','UNDOTBS1','XDB')) loop*/ for ts in (SELECT TS# NUM_TS,NAME AS TABLESPACE_NAME FROM v$TABLESPACE WHERE NAME NOT IN ('CWMLITE','DRSYS','INDX','ODM','SYSTEM','TEMP','UNDOTBS1','XDB') order by name)LOOP TotalDataBytes:=0; OccupiedByOthers:=0; TotalFreeListSpace:=0; TotalUnusedBlocks:=0; TotalSize:=0; TotalIndexSize:=0; 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 s.tablespace_name = ts.TABLESPACE_NAME -- 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 AND t.partitioned = 'NO' ORDER BY t.owner, t.table_name ) loop if no_rows then 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 || '>.'||ts.TABLESPACE_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 ); 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; end loop; SELECT nvl(sum(bytes)/1024,0) INTO TotalIndexSize FROM dba_indexes i, dba_segments s WHERE s.tablespace_name = ts.TABLESPACE_NAME 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 OccupiedByOthers := TotalSize - ( (TotalDataBytes/1024) + (TotalFreeListSpace/1024) + (TotalUnusedBlocks*v_block_size_Kbytes) ); SummaryTitle := 'Data segment summary for tablespace <'||ts.TABLESPACE_NAME||'>'; dbms_output.put_line('-------------------------------------------------------------------------------------------------------------------------------'); 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/1024,'9G999G999') || ' Mbytes ' || to_char(case TotalSize when 0 then 0 else (TotalDataBytes/1024)*(100/TotalSize) end,'990D99') || ' %' ); dbms_output.put_line( 'Total storage overhead : ' || to_char(OccupiedByOthers/1024,'9G999G999') || ' Mbytes ' || to_char(case TotalSize when 0 then 0 else OccupiedByOthers*(100/TotalSize) end,'990D99') || ' %' ); dbms_output.put_line( 'Total free space for inserts below HWM : ' || to_char(TotalFreeListSpace/1024/1024,'9G999G999') || ' Mbytes ' || to_char(case TotalSize when 0 then 0 else (TotalFreeListSpace/1024)*(100/TotalSize) end,'990D99') || ' %' ); dbms_output.put_line( 'Total free space above high water marks : ' || to_char(TotalUnusedBlocks*v_block_size_Kbytes/1024,'9G999G999') || ' Mbytes ' || to_char(case TotalSize when 0 then 0 else TotalUnusedBlocks*v_block_size_Kbytes*100/TotalSize end,'990D99') || ' %' ); dbms_output.put_line( '--------------------------------------------------------------------------' ); dbms_output.put_line( 'Total allocated space (data segments : ' || to_char(TotalSize/1024,'9G999G999') || ' Mbytes 100,00 %' ); dbms_output.put_line( 'Total allocated space (index segments) : ' || to_char(TotalIndexSize/1024,'9G999G999') || ' Mbytes' ); dbms_output.put_line( '--------------------------------------------------------------------------' ); dbms_output.put_line( 'Total allocated space : ' || to_char((TotalSize + TotalIndexSize)/1024,'9G999G999') || ' Mbytes' ); select sum(bytes)/1024/1024 into TSTotalSize from v$datafile where ts# = ts.NUM_TS; dbms_output.put_line( 'Total space : ' || to_char(TSTotalSize,'9G999G999') || ' Mbytes' ); end if; end loop; end; / PROMPT SET FEEDBACK ON SET PAGESIZE 9999 UNDEFINE DefaultStorageEfficiency UNDEFINE ruta_fichero SPOOL OFF