Logo Oracle Deutschland   DBA Community  -  März 2014
Online Table Redefinition mit DBMS_REDEFINITION - 12c Update
von Ulrike Schwinn, Oracle Deutschland B.V. & Co. KG

Einige Operationen im Bereich der Reorganisation oder Neudefinition von Datenbankobjekten müssen von Zeit zu Zeit offline oder online im laufenden Datenbankbetrieb durchgeführt werden. Dazu gehören beispielsweise Änderungen an Datentypen (z.B. LONG zu BASICFILE; BASICFILE zu SECUREFILE), Tranformationen von Tabellen (z.B. nicht partitionierte in partitionierte Tabelle) oder Änderungen an der Speicherung (z.B. Komprimierungs- Einstellungen). Die Durchführung von Online Table Operationen erfolgt dabei entweder über ein entsprechendes SQL Kommandos - soweit vorhanden - oder wird über das Package DBMS_REDEFINITION unterstützt. Ein wichtiges Kennzeichen einer ONLINE Redefinition ist dabei, dass Abfragen und DML Operationen während des Redefinitionsprozesses ohne Unterbrechung weiter laufen können.

Häufig existiert allerdings kein entsprechendes SQL Kommando, so dass die Nutzung von DBMS_REDEFINITION unumgänglich ist. Ein Beispiel dafür wäre das ALTER TABLE MOVE ONLINE Kommando, das zwar eine Index Organized Table (kurz IOT) wieder aufbaut, allerdings nicht für heap organisierte Tabelle verwendet werden kann; ein weiteres Beispiel liefert die Transformation von BASICFILE in SECURFILE Speicherung.

Generell gibt es in jedem Datenbankrelease eine Erweiterung bzgl. der Online Operationen. In Oracle Database 12c beispielsweise handelt sich dabei um folgende Neuerungen:

  • Löschen eines Index (DROP INDEX ... ONLINE)
  • Index als UNUSABLE markieren (ALTER INDEX ... UNUSABLE ONLINE)
  • Spalte als UNUSED markieren (ALTER TABLE ... SET UNUSED COLUMN ... ONLINE)
  • Löschen eines Constraints (ALTER TABLE ... DROP CONSTRAINT ... ONLINE)
  • MOVE einer Tabellen Partition/Subpartition (ALTER TABLE ... MOVE PARTITION ... ONLINE)
  • DATAFILE MOVE (ALTER DATABASE MOVE DATAFILE ...)
Einen Überblick über den aktuellen Stand der Online Operationen finden Sie auf OTN oder im Handbuch Oracle Database High Availability Overview 12c Release 1 (12.1) Kapitel 5.3

Auch im Bereich DBMS_REDEFINTION sind einige interessante Features in Oracle Database 12c hinzugefügt worden. Der folgende Tipp gibt zuerst eine kurze Einführung in die Nutzung von DBMS_REDEFINITION und diskutiert danach die Neuerungen in Oracle Database 12c.

Einführung - Stand in Oracle Database 11g
Generell ist die Verwendung von DBMS_REDEFINITION immer dann erforderlich (Stand 11g), wenn keine entsprechenden SQL Kommandos für die online Durchführung zur Verfügung stehen. Folgende Liste gibt einige Beispiele für typische Anwendungen:
  • Änderung an Speicherparametern
  • Hinzufügen von Spalten
  • Änderung der Komprimierungseinstellungen
  • Verlagerung in einen anderen Tablespace
  • Veränderung an der Partitionierungsdefinition der Tabelle
  • Konvertierung einer relationalen Tabelle in eine Object Tabelle und vice versa
  • Änderung der LOB Eigenschaft von BASICFILE nach SECUREFILE und vice versa
Das Package DBMS_REDEFINITION wird übrigens meist über den Linemode Aufruf genutzt; eine teilweise graphische Implementierung findet man im Enterprise Manager Cloud Control über den "Reorganize Object Wizard" im Bereich der Tabellen Administration.



Die Verwendung von DBMS_REDEFINITION ist dabei als Mehrschritt Verfahren zu implementieren. Um die Vorgehensweise kurz zu demonstrieren gibt folgende Liste einen Überblick über den Standard Ablauf:
  1. (optional) Aktiviere ROW Movement bzw. Parallelisierung.
  2. Prüfe mit der Prozedur CAN_REDEF_TABLE, ob die Tabelle redefiniert werden kann.
  3. Erzeuge eine leere Interims Tabelle im selben Schema mit allen gewünschten Eigenschaften.
  4. Starte den Redefinition Prozess durch Aufruf von START_REDEF_TABLE.
  5. Kopiere die abhängigen Objekte (wie z.B. Trigger, Indizes, Materialized View Logs, Grants und Constraints) und Statistiken entweder manuell oder automatisch über die Prozedur COPY_TABLE_DEPENDENTS.
  6. FINISH_REDEF_TABLE schliesst die Redefinition ab. Für eine kurze Zeit wird die Tabelle dabei mit einem exklusiven Lock gesperrt. Allerdings wartet FINISH_REDEF_TABLE auf Pending DML um die Redefinition zu beenden.
  7. Lösche die Tabelle.
Als Ergebnis steht nun die Original Tabelle mit den neuen Eigenschaften, den Indizes, Constraints, Trigger und Statistiken der Interims Tabelle zur Verfügung. PL/SQL Objekte, die von redefinierten Tabellenelementen abhängig sind, können dabei allerdings den Status INVALID haben. Weitere Aufgaben wie Abbrechen, Registrieren und zwischenzeitliches Synchronisieren der Interims Tabelle können über die Funktionen ABORT_REDEF_TABLE, REGISTER_DEPENDENT_OBJECT und SYNC_INTERIM_TABLE implementiert werden. Einige anschauliche und vollständige Beispiele finden sich im Handbuch Oracle Database Administrator's Guide 12c Release 1 (12.1) im Kapitel Redefining Tables Online oder im Blogeintrag zu Neudefinition von Tabellen im Online Betrieb mit DBMS_REDEFINITION . Zu beachten ist übrigens auch, dass es Einschränkungen gibt, die beispielsweise hier nachzulesen sind.

Neuerungen in Oracle Database 12c
Die neuen Features lassen sich in die Bereiche höhere Verfügbarkeit, Verarbeitung im Batch Betrieb, Vereinfachung der Handhabung und Verminderung von Restriktionen einordnen. So ist beispielsweise "Mehr Funktionalität mit weniger Code" ein wichtiges Leitthema. In den folgenden Abschnitten werden die Neuerungen im Einzelnen erläutert.

Online Redefinition in einem (1) Schritt
Für einige wichtige Operationen bietet die neue Prozedur REDEF_TABLE eine Anwendung in einem einzigen Schritt. Es ist also keine Implementierung eines Mehrschrittverfahrens erforderlich. Folgende Operationen werden dabei unterstützt:
  • Tablespace Änderungen
  • Änderungen des Compression Typs (für Tabellen, Partitionen, sogar Indizes oder LOB Spalten)
  • Änderungen an LOB Spalten (SECUREFILE, BASICFILE Speicherung)
Um die Funktionsweise zu demonstrieren, gehen wir von folgendem Sachverhalt aus: Die Tabelle PRODUCTS ist unkomprimiert und soll online komprimiert werden.
SQL>  SELECT table_name, compress_for, compression 
      FROM user_tables WHERE table_name ='PRODUCTS';
TABLE_NAME                COMPRESS_FOR    COMPRESS
------------------------- --------------- --------
PRODUCTS
                                 
-- die Prozedur REDEF_TABLE
PROCEDURE REDEF_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 TNAME                          VARCHAR2                IN
 TABLE_COMPRESSION_TYPE         VARCHAR2                IN     DEFAULT
 TABLE_PART_TABLESPACE          VARCHAR2                IN     DEFAULT
 INDEX_KEY_COMPRESSION_TYPE     VARCHAR2                IN     DEFAULT
 INDEX_TABLESPACE               VARCHAR2                IN     DEFAULT
 LOB_COMPRESSION_TYPE           VARCHAR2                IN     DEFAULT
 LOB_TABLESPACE                 VARCHAR2                IN     DEFAULT
 LOB_STORE_AS                   VARCHAR2                IN     DEFAULT
Nun können wir in einem einzigem Schritt die Komprimierung der Tabelle PRODUCTS ändern.
exec DBMS_REDEFINITION.REDEF_TABLE (
        uname                  => 'SH', 
        tname                  => 'PRODUCTS', 
        table_compression_type => 'ROW STORE COMPRESS ADVANCED')
In einer parallelen Session kann man übrigens nachsehen, welche Objekte während der Online Redefinition verwendet werden.
SQL> SELECT object_type, object_owner, object_name, interim_object_name 
     FROM dba_redefinition_objects;

OBJECT_TYP OBJEC OBJECT_NAME               INTERIM_OBJECT_NAME
---------- ----- ------------------------- ---------------------------------
INDEX      SH    PRODUCTS_PK               TMP$$_PRODUCTS_PK0
INDEX      SH    PRODUCTS_PROD_CAT_IX      TMP$$_PRODUCTS_PROD_CAT_IX0
INDEX      SH    PRODUCTS_PROD_STATUS_BIX  TMP$$_PRODUCTS_PROD_STATUS_0
CONSTRAINT SH    SYS_C009946               TMP$$_SYS_C0099460
...
CONSTRAINT SH    SALES_PRODUCT_FK          TMP$$_SALES_PRODUCT_FK0
TABLE      SH    PRODUCTS                  REDEF$_T119640
INDEX      SH    PRODUCTS_PROD_SUBCAT_IX   TMP$$_PRODUCTS_PROD_SUBCAT_0
25 rows selected.
Wichtig zu wissen ist, dass weder einzelne LOB Spalten noch einzelne Indizes angegeben werden können. Die Operationen werden immer für alle betroffenen Objekte der Tabelle ausgeführt.

Online Table Redefinition mit mehreren Partitionen
Will man mehrere Partitionen gleichzeitig redefinieren, muss man bis einschliesslich 11g mehrere Sessions dazu verwenden. Ab Oracle Database 12c ist es möglich mehrere Partitionen in einer einzigen Session zu reorganisieren. Die Prozeduren CAN_REDEF_TABLE, START_REDEF_TABLE, SYNC_INTERIM_TABLE und FINISH_REDEF_TABLE erlauben nun im Argument PART_NAME eine Liste von Partitionen anzugeben. Zusätzlich garantiert das neue Argument CONTINUE_AFTER_ERRORS eine Fortsetzung der Redefinition auch im Fehlerfall (der Neudefinition einer Partition). Folgendes Beispiel zeigt ein abschliessendes FINISH_REDEF_TABLE mit der neuen Funktionalität.
BEGIN 
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(
   uname        => 'SH', 
   orig_table   => 'salestable', 
   int_table    => 'int_salestb1, int_salestb2',
   col_mapping  => NULL,
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
   part_name    => 'sal10q1, sal10q2',
   continue_after_errors => TRUE);
END;
/
Möchte man den Status der Redefinition der einzelnen Partitionen überwachen, kann man die View DBA_REDEFINITION_STATUS verwenden. Dort wird beispielsweise angegeben, welche Partition erfolgreich redefiniert worden ist.
SQL> SELECT base_table_name, base_partition_name, 
     interim_object_name, prev_operation, status, restartable 
     FROM dba_redefinition_status;

BASE_TABLE_NAME BASE_PARTITION_NAME  INTERIM_OBJECT_NAME  PREV_OPERATION
--------------- -------------------- -------------------- ------------------
STATUS  R
------- -
SALESTABLE      SAL10Q1              INT_SALESTB1         START_REDEF_TABLE
Success N

SALESTABLE      SAL10Q2              INT_SALESTB2         START_REDEF_TABLE
Success N
Das vollständige Beispiel findet sich übrigens im Handbuch Oracle Database Administrator's Guide 12c Release 1 (12.1) in Kapitel 20 Example 5

Verschiedenes
Unter dem Punkt Verschiedenes möchte ich abschliessend noch zwei weitere Ergänzungen zusammenfassen. Die erste Erweiterung ist im Bereich der Restriktionen zu finden: Online Redefinition mit VPD Policies ist nun ab Oracle Database 12c mit DBMS_REDEFINITION möglich. Hierzu zeigt der neue Parameter COPY_VPD_OPT mit den Werten DBMS_REDEFINITION.CONS_VPD_AUTO bzw. DBMS_REDEFINITION.CONS_VPD_MANUAL beim START_REDEF_TABLE an, wie mit den Policies verfahren werden soll.

Um die Downtime beim FINISH_REDEF_TABLE zu reduzieren, wurde ein weiterer Parameter zur Verfügung gestellt. Mit dem neuen Argument DML_LOCK_TIMEOUT kann man nun die Zeit (in Sekunden) angeben, auf die bei Pending DMLs gewartet werden soll. Ist die Zeit abgelaufen, wird die Redefinition automatisch so beendet, dass ein erneutes FINISH_REDEF_TABLE möglich ist. Falls der Parameter NULL (Default) ist, wird die Prozedur nicht automatisch beendet, sondern muss manuell abgebrochen werden. Dies ermöglicht allerdings kein erneutes FINISH_REDEF_TABLE. In diesem Fall muss nach einen ABORT_REDEF_TABLE wieder mit START_REDEF_TABLE gestartet werden.

Lizenzierung
Die Nutzung des Package DBMS_REDEFINITION erfordert die Lizenzierung der Enterprise Edition.

Zurück zur Community-Seite