Als PDF Downloaden!

Tipps & Tricks: Dezember 2005
Bereich:
DBA, SQL
Erstellung:
12/2005 MP
Versionsinfo:
10.1, 10.2, 11.1, 11.2
Letzte Überarbeitung:
06/2009 MA
ALTER TABLE SHRINK
Neue Funktionalität in 10g: ALTER TABLE SHRINK
Wer kennt nicht dieses Problem: Es gibt in der Datenbank eine große Tabelle, aus der viele Datensätze gelöscht
wurden. Die Datensätze sind zwar gelöscht, aber der physikalische Platz in dem Tablespace wird nicht
freigegeben, und die "HIGH WATER MARK" wird nicht verändert.
Die Möglichkeiten, die "HIGH WATER MARK" zurückzusetzen, die es bislang gab, sind: ALTER TABLE MOVE
TABLESPACE bzw. EXPORT / IMPORT. Bei diesen Optionen ist jedoch eine Ausfallzeit gegeben.
In der Version 10g ist es jetzt machbar, die "HIGH WATER MARK" zurückzusetzen, ohne die Tabelle zu sperren:
Und zwar mit Hilfe von SHRINK_CLAUSE des Befehls ALTER TABLE. Mit der SHRINK_CLAUSE können
Tabellen, Indizes, indexorganisierte Tabellen, Partitionen, Subpartitionen, LOB Segmente (ab Version 10.2) und
Materialized Views manuell verkleinert werden.
Voraussetzungen:
Oracle RDBMS ab Version 10g.
Der Tablespace muss mit der Option SEGMENT MANAGEMENT AUTO angelegt sein.
Bei der Tabelle, die verkleinert werden soll, muss ROW MOVEMENT aktiviert sein. Die Tabelle muss
reorganisiert werden und damit ändern sich die ROWIDS.
Einschränkungen:
Die Tabelle darf nicht komprimiert sein.
Die Tabelle darf keine FUNCTION BASED Indizes besitzen.
Die Mastertabelle einer ON COMMIT MATERIALIZED VIEW kann nicht verkleinert werden.
ROWID MATERIALIZED VIEWS müssen nach dem Verkleinern neu aufgebaut werden.
Die Tabelle darf keine LOB oder LONG Spalten besitzen (nur in Version 10.1).
Beispiel:
In diesem Beispiel verwende ich eine Tabelle BIGEMP mit ca. 7,5 Mio. Datensätzen.
Als erstes wird überprüft, wie viele Blöcke und Extents von der Tabelle allokiert sind.
Select BLOCKS, EXTENTS from USER_SEGMENTS where SEGMENT_NAME = 'BIGEMP';
BLOCKS
-----45696
EXTENTS
---------116
Als weitere Ausgangswerte dienen die leeren Blöcke und der durchschnittliche freie Platz innerhalb eines
Blockes. Diese Werte werden bei einem ANALYZE TABLE-Befehl aktualisiert.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 3
ANALYZE Table BIGEMP compute statistics;
Select EMPTY_BLOCKS, AVG_SPACE from USER_TABLES where table_name = 'BIGEMP';
EMPTY_BLOCKS AVG_SPACE
------------ ---------1019
838
Nun wird jeder zweite Datensatz gelöscht und die Tabelle neu analysiert.
Delete from bigemp where empno in(7369, 7521, 7654, 7782, 7839, 7876, 7902);
Commit;
ANALYZE Table BIGEMP compute statistics;
Bei der folgenden Prüfung auf Blöcke und Extents hat sich keine Veränderung ergeben.
Select BLOCKS, EXTENTS from USER_SEGMENTS where SEGMENT_NAME = 'BIGEMP';
BLOCKS
EXTENTS
------ ---------45696
116
Die Prüfung der leeren Blöcke und des freien Patzes ergibt, dass die Blöcke unverändert sind, aber der freie Platz
größer geworden ist.
Select EMPTY_BLOCKS, AVG_SPACE from USER_TABLES where table_name = 'BIGEMP';
EMPTY_BLOCKS AVG_SPACE
------------ ---------1019
4254
Jetzt starten wir mit dem Verkleinern der Tabelle. Dazu wird ROW MOVEMENT aktiviert, die Tabelle verkleinert
und neu analysiert.
Alter Table BIGEMP ENABLE ROW MOVEMENT;
Alter Table BIGEMP SHRINK SPACE;
ANALYZE Table BIGEMP compute statistics;
Bei der abschließenden Prüfung auf Blöcke und Extents kann festgestellt werden, dass die Anzahl der Blöcke und
Extents deutlich abgenommen hat.
Select BLOCKS, EXTENTS from USER_SEGMENTS where SEGMENT_NAME = 'BIGEMP';
BLOCKS
EXTENTS
------ ---------21208
92
Auch die Anzahl der leeren Blöcke und der freie Platz sind nach dem SHRINK deutlich kleiner geworden.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 3
Select EMPTY_BLOCKS, AVG_SPACE from USER_TABLES where table_name = 'BIGEMP';
EMPTY_BLOCKS
-----------187
AVG_SPACE
--------396
Zusammenfassung:
Die neue Funktionalität ALTER TABLE SHRINK ist ideal dafür geeignet, im laufenden Betrieb Tabellen zu
verkleinern und die HIGH WATER MARK zurückzusetzen. Es kann somit auf einfache Art ungenutzter Platz
innerhalb eines Tablespaces zur Verfügung gestellt werden.
Mehr Informationen zu diesem Thema und zu weiteren Funktionen in 10g erhalten Sie auch in unserem Kurs:
Neuerungen von Oracle 10g (MS 1080).
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 3