Oft ist es notwendig, sich neben der reinen Größe einer Tabelle auch den Füllgrade anzeigen zu lassen:
(Die aktuelle Version liegt immer hier OraPowerShell SQL Library - space.sql )
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
Guter Vorschlag für die Reorganisation von Tabellen und Tablespaces: