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...