Skip to Main Content
 
Titelbild Muniqsoft Training

Search Results

Oracle Tipp Text

Berechnung des Segmentfüllpegels

Bereich:DBA:PL/SQL, Version: ab RDBMS 12.x, Letzte Überarbeitung: 14.12.2018

Keywords:DBA , PL/SQL

In diesem Tipp des Monats schauen wir uns mal die Speicherbelegung der Tabellen und Indizes an.
Es gibt zwar seit Version 10g einen Advisor, der einem die Speicherbelegung der Segmente anzeigt; wir wollen hier aber eine Ausgabe in Tabellenform für mehrere Objekte erzeugen.
Wie heißt es so schön: Es gibt kaum eine Information, die man nicht aus einer Oracle-DB herausholen kann, man muss nur die richtige (Öl)-Quelle anzapfen.
Dieser Trick beruht auf dem Package DBMS_SPACE, das die Zahl der Blöcke anzeigen kann, die jeweils bis

                       <= 25%,
                       <= 50%,
                       <= 75%
                oder <=100% gefüllt sind.

Diese Zahlen werden für jedes Segment zusammengezählt und vom Package zurückgegeben.
Die Aufgabe meiner Funktion ist nur, das Ergebnis in Tabellenform darzustellen.

CREATE OR REPLACE TYPE space_seg_type
AS OBJECT (
owner       VARCHAR2(30),
seg_name    VARCHAR2(30),
o_type      VARCHAR2(30),
"F<=25%"    NUMBER,
"F<=50%"    NUMBER,
"F<=75%"    NUMBER,
"F<=100%"   NUMBER,
FULL_BLOCKS NUMBER,
FREE_BYTES  NUMBER,
TOTAL_BYTES NUMBER,
PER_FREE    NUMBER);
/

 

CREATE OR REPLACE TYPE space_seg_tab_type as table of space_seg_type;
/


Der Benutzer des Package benötigt zwei Rechte DIREKT!

GRANT SELECT ON sys.dba_segments TO system;
GRANT ANALYZE ANY TO system;

 

CREATE OR REPLACE
FUNCTION get_space_info
(p_owner                IN VARCHAR2 DEFAULT NULL,
 p_name                 IN VARCHAR2 DEFAULT NULL,
 p_tablespace           IN VARCHAR2 DEFAULT NULL)
RETURN space_seg_tab_type PIPELINED IS
   v space_seg_type;
   v_unformatted_blocks number;
   v_unformatted_bytes number;
   v_fs1_blocks    number;
   v_fs1_bytes     number;
   v_fs2_blocks    number;
   v_fs2_bytes     number;
   v_fs3_blocks    number;
   v_fs3_bytes     number;
   v_fs4_blocks    number;
   v_fs4_bytes     number;
   v_full_blocks   number;
   v_full_bytes    number;
   v_seg_name VARCHAR2(30);
BEGIN
   FOR r IN (SELECT owner,segment_name,segment_type,bytes FROM
             dba_segments
             WHERE owner=nvl(p_owner,owner)
             AND segment_name=nvl(p_name,segment_name)
             AND tablespace_name=nvl(p_tablespace,tablespace_name)
        AND tablespace_name NOT IN ('SYSTEM')
        AND segment_type NOT IN ('LOBINDEX', 'LOBSEGMENT')) LOOP
      BEGIN
        v_seg_name:=r.segment_name;
            -- Neuen Speicherplatz für ein Element im Array reservieren
            -- Package zur Anzeige des Blockfüll-Pegels starten
        dbms_space.space_usage (r.owner, r.segment_name, r.segment_type,
            v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks,
            v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks,
            v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks,
            v_full_bytes);
            -- Ausgabezeile vorbereiten
        v:=space_seg_type(r.owner, r.segment_name, r.segment_type,
            v_fs1_blocks, v_fs2_blocks, v_fs3_blocks, v_fs4_blocks,
            v_full_blocks,
            v_fs1_bytes+v_fs2_bytes+v_fs3_bytes+v_fs4_bytes, r.bytes,
            round((v_fs1_bytes+v_fs2_bytes+v_fs3_bytes+v_fs4_bytes)/r.bytes*100,2));
            --Zeile zurückgeben
        PIPE ROW (v) ;
            -- Im Fehlerfall nur Elementname zurückgeben
      EXCEPTION
        WHEN OTHERS THEN
            v:=space_seg_type(substr(sqlerrm,1,29), v_seg_name, null,
               null, null, null, null, null, null, null, null);
            PIPE ROW (v) ;
      END;
   END LOOP;
   RETURN; -- in 9i erforderlich!
END;
/
show errors


In 9i ist die RETURN-Klausel am Ende der Funktion zwingend erforderlich, in 10g kann sie auch weggelassen werden.

Beispiel für alle Objekte aus einem Schema:

select * from TABLE(get_space_info('SCOTT'));


Beispiel für ein Objekt aus einem Schema:

select * from TABLE(get_space_info('SCOTT','EMP'));


Beispiel für alle Objekte aus einem Tablespace:

select * from TABLE(get_space_info(null, null, 'USERS'));


Wenn Sie möchten, legen Sie doch eine View über den Select, dann sieht es richtig professionell aus:

CREATE OR REPLACE VIEW segment_usage AS
select * from TABLE(get_space_info('SCOTT'));


Die Ausgabe könnte dann so aussehen:

select* from TABLE(get_space_info('SCOTT'));
OWNER   SEG_NAME O_TYPE F<=
25%     F<=
50%     F<=
75%     F<=
100%    FULL_
BLOCKS  FREE_
BYTES   TOTAL_
BYTES   PER_
FREE
-----  --------------  -----  -  -  -  ---  ---  -------  -------  -----
SCOTT  TA_AKTION       TABLE  2  1  1  30   210  278528   2097152  13,28
SCOTT  TA_AKTION_DEL   TABLE  2  3  2  273  90   2293760  3145728  72,92
SCOTT  TA_FEHLER       TABLE  0  0  0  5    0    40960    65536    62,5
SCOTT  TA_FIRMA        TABLE  0  1  0  19   160  163840   2097152  7,81
SCOTT  PK_FIRMA        INDEX  0  4  0  0    9    32768    131072   25
SCOTT  TA_EM           TABLE  2  6  9  344  323  2957312  6291456  47,01
SCOTT  PK_KONTAKT      INDEX  0  1  0  0    19   8192     196608   4,17
SCOTT  TA_KONTAKT_DEL  TABLE  0  5  1  412  62   3424256  4194304  81,64
SCOTT  TA_PERSON       TABLE  0  1  1  18   130  163840   2097152  7,81
SCOTT  PK_PERSON       INDEX  0  5  0  0    15   40960    196608   20,83
SCOTT  TA_PERSON_DEL   TABLE  3  7  6  663  415  5562368  9437184  58,94
SCOTT  TA_TOUCH        TABLE  0  1  1  5    173  57344    2097152  2,73


In SQL*Plus kann man die Spalten-Längen noch formatieren.
Beispiel:

COL "F<25%" FORMAT 9999

In der Ausgabe sehen Sie in der letzten Spalte den ungenutzten Platz des Segments in Prozent. Auf Basis dieser Zahl kann man entscheiden, ob eine Reorganisation des Segments nötig ist.

Zu Risikien und Nebenwirkungen:

Die Information über den Füllpegel kann nicht von Objekten aus dem SYSTEM-Tablespace oder einem Tablespace ohne SEGMENT SPACE MANAGEMENT AUTO Option erzeugt werden.

Bei einem großen Schema kann die Ausgabe etwas dauern und einige Ressourcen verbrauchen.

  • Der Eigentümer der Funktion benötigt ein CREATE TYPE Recht und ein Leserecht auf die View DBA_SEGMENTS

Wenn Sie durch dieses Beispiel Lust auf mehr bekommen haben, besuchen Sie doch einen unserer DB Monitoring oder DB Tuning Kurse. Da machen wir noch ganz andere Sachen :-)

Wer sich für die Pipelined Row interessiert, ist in unserem PL/SQL gut aufgehoben.

Viel Spaß beim Segment-Beobachten...



Weitere Interessente Artikel zum Thema:



Empfohlene Schulungen zum Thema:
2026.02.18