Neudefinition von Tabellen im Online Betrieb mit DBMS

DBA Community - Juli 2008

Neudefinition von Tabellen im Online Betrieb mit DBMS_REDEFINITION

von Ulrike Schwinn, ORACLE Deutschland GmbH

Online Operationen sind meistens unverzichtbare Operationen für den laufenden Datenbankbetrieb. Seit Oracle 8i werden ONLINE Operationen wie z.B. der online Index Aufbau oder die online Neudefinition von Tabellen unterstützt. Dabei wird der Support in jedem neuen Datenbankrelease erweitert. Die Neudefinition von Tabellen Online durchzuführen, ist ein wichtiger Bestandteil der Datenbank und immer dann erforderlich, wenn die Struktur oder die Speicherung der Tabelle online geändert werden soll.
Folgende Beispiele zeigen nur einige Anwendungsszenarios auf:

Übergang von Nicht partitionierter Tabelle zu einer partitionierten Tabelle

Umschalten der Speicherung auf Komprimierung

Migration von LONG nach LOB oder nach SECUREFILEs

Hinzufügen oder Änderung von Spalten

Verlagerung einer Tabelle in einen anderen Tablespace

Änderung der physikalischen Eigenschaften einer einzelnen Partition

Diese Online Redefinition steht mit der Enterprise Edition zur Verfügung und ist durch das spezielle Package DBMS_REDEFINITION unterstützt. Dabei stellt dieses Package die Basistechnologie zur Neudefinition von Tabellen dar. Eine teilweise graphische Implementierung findet man im Enterprise Manager im Segment Advisor oder bei der Anwendung des "Reorganize Object Wizards" im Bereich der Tabellen Administration.

Der folgende Tipp wird sich auf die online Neudefinition von Tabellen konzentrieren und an 2 Beispielen die Verwendung demonstrieren.

Beispiel 1 soll folgende Aktionen durchführen: Hinzufügen von Spalten, Änderungen der Defaultwerte, Konvertierung von LONG nach LOB Datentyp. Dazu soll folgende einfache Ausgangssituation gelten:

CREATE TABLE admin_emp 
(empno    NUMBER(5) PRIMARY KEY, 
 ename    VARCHAR2(15) NOT NULL, 
 job      VARCHAR2(10),
 cv       LONG,
 deptno   NUMBER(3) NOT NULL CONSTRAINT  admin_dept_fkey REFERENCES dept(deptno))
/
CREATE INDEX i_ename ON admin_emp(ename);

INSERT INTO admin_emp VALUES (2,'SCHWINN','Vertrieb','xxxx',10);
INSERT INTO admin_emp VALUES (3,'DURBEN','Training',null,20);
INSERT INTO admin_emp VALUES (5,'FABRY','Vertrieb','yyyyyy',10);
INSERT INTO admin_emp VALUES (1,'MAYER','Training',null,10);
COMMIT;

CREATE OR REPLACE PROCEDURE test_proc
AS
t NUMBER;
BEGIN 
SELECT count(*) INTO t FROM admin_emp;
END;
/ 

CREATE OR REPLACE TRIGGER test_deptno
BEFORE UPDATE OF deptno ON admin_emp
FOR EACH ROW
BEGIN
  :NEW.deptno := 10;
END;
/
EXECUTE dbms_stats.gather_table_stats(null,'ADMIN_EMP');

PL/SQL procedure successfully completed.

Bevor die Redefinition gestartet wird, kann optional überprüft werden, ob die Tabelle den Anforderungen der Redefinition auch genügen kann. Falls kein Primary Key zur Verfügung steht, kann auch die ROWID Methode eingesetzt werden und mit dbms_redefinition.cons_use_rowid angezeigt werden. Nach erfolgreicher Durchführung wird die leere Zieltabelle erzeugt, wie im Folgenden zu sehen ist:

SQL> EXECUTE dbms_redefinition.can_redef_table('SCOTT','ADMIN_EMP',dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

CREATE TABLE int_admin_emp 
(empno NUMBER(5) not null, 
 ename VARCHAR2(15), 
 job VARCHAR2(10),  
 cv  CLOB, 
 deptno NUMBER(3), 
 bonus NUMBER (7,2) DEFAULT(1000))
 /

Bei jeder Redefinition muss zusätzlich ausreichend Speicherplatz garantiert sein, da die Redefinition im Hintergrund über MV und temporäre Objekte realisiert wird. Vorab ist es immer notwendig manuell die Zieltabelle als leere Tabelle zur Verfügung zu stellen. In unserem Fall enthält die temporäre Zieltabelle INT_ADMIN_EMP die zusätzliche Spalte BONUS mit Defaultwert 1000 und den neuen Datentyp CLOB. Anzumerken ist, dass dabei keine Constraints berücksichtigt werden müssen. Ab Oracle Database 10g ist es möglich Objekte wie Indizes, Trigger, Constraints usw. automatisch zu klonen; kein manuelles Erzeugen ist mehr notwendig.

Nun kann die Redefinition losgehen!
Mit dbms_redefinition.start_redef_table wird die Redefinition gestartet. Bei Änderungen an den Spalten, wie in unserem Fall, muss das Spaltenmapping genau angegeben werden. Zusätzlich können die Spalten dabei initial sortiert werden wie z.B. nach der Spalte EMPNO. Die Prozedur dbms_redefinition.copy_table_dependents klont in unserem Beispiel die Statistiken, Indizes, Constraints, und Trigger. Ab Oracle Database 11g auch die MV Logs. Nach Beendigung der Redefinition, sind die Statistiken kopiert, alle kopierten Objekte erhalten ihren ursprünglichen Namen, und die Constraints und Trigger haben den Status ENABLED.

Folgender Ablauf demonstriert die Verwendung:

SQL> EXECUTE dbms_redefinition.start_redef_table('SCOTT','ADMIN_EMP','INT_ADMIN_EMP', -
     'empno empno,ename ename, job job, to_lob(cv) cv, deptno+10 deptno, 0 bonus', orderby_cols=>'EMPNO');
PL/SQL procedure successfully completed.

SQL> CREATE INDEX int_i_ename ON int_admin_emp(ename) TABLESPACE comp;
Index created.

SQL> EXECUTE dbms_redefinition.register_dependent_object('SCOTT', 'ADMIN_EMP', 'INT_ADMIN_EMP', -
dbms_redefinition.cons_index, 'SCOTT', 'I_ENAME' , 'INT_I_ENAME');
PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> DECLARE  error_count binary_integer;
  2  BEGIN
  3  dbms_redefinition.copy_table_dependents('SCOTT', 'ADMIN_EMP', 'INT_ADMIN_EMP', 
  4  copy_indexes     => dbms_redefinition.cons_orig_params,
  5  copy_triggers    => true, 
  6  copy_constraints => true, 
  7  copy_privileges  => true, 
  8  ignore_errors    => false, 
  9  num_errors       => error_count,
  10 copy_statistics  => TRUE, 
  11 copy_mvlog       => FALSE);
  12 dbms_output.put_line('errors := ' || to_char(error_count));
  13 END;
  14 /
errors := 0
PL/SQL procedure successfully completed.

Manchmal ist es notwendig, die Objekte manuell zu erzeugen; zum Beispiel, wenn ein Index in einem anderen Tablespace (siehe oben) angelegt werden soll. Dies ist möglich mit der Prozedur register_dependent_object, die vor dem Klonen der Objekte, die manuelle Registrierung anzeigt. Die nachfolgende Prozedur copy_table_dependents ignoriert dann einfach die registrierten Objekte beim Klonen.
Zum Monitoren eignen sich spezielle Data Dictionary Views dbms_redefinition_errors bzw. dbms_redefinition_objects. Letztere gibt einen genauen Überlick über die geklonten bzw. manuell registrierten Objekte. Folgende Abfrage zeigt die Ausgabe in unserem Beispiel:

SQL> SELECT object_name,interim_object_name, object_type FROM dba_redefinition_objects;

OBJECT_NAME                    INTERIM_OBJECT_NAME            OBJECT_TYPE
------------------------------ ------------------------------ ------------
I_ENAME                        INT_I_ENAME                    INDEX
SYS_C0014597                   TMP$$_SYS_C00145970            CONSTRAINT
SYS_C0014598                   TMP$$_SYS_C00145980            CONSTRAINT
SYS_C0014599                   TMP$$_SYS_C00145990            CONSTRAINT
ADMIN_DEPT_FKEY                TMP$$_ADMIN_DEPT_FKEY0         CONSTRAINT
TEST_DEPTNO                    TMP$$_TEST_DEPTNO0             TRIGGER
ADMIN_EMP                      INT_ADMIN_EMP                  TABLE
SYS_C0014599                   TMP$$_SYS_C00145990            INDEX

8 rows selected.

Die Redefinition wird mit der Prozedur finish_redef_table abgeschlossen. Danach steht die ursprüngliche Tabelle mit den Eigenschaften der Zieltabelle, den zugehörigen Indizes, Statistiken, Trigger und Constraints mit Status ENABLED zur Verfügung. Cursor, Prozeduren, Trigger und anderer abhängige Objekte sind bis einschliesslich 10g im Status INVALID und werden bei der nächsten Anwendung rekompiliert. In 11g hingegen wird die Notwendigkeit des Rekompilierens minimiert. Falls die Redefinition die PL/SQL Objekte wie Packages, Prozeduren, Synomyme nicht logisch betrifft, ist die Rekompilierung nicht notwendig. Trigger sind von dieser Eigenschaft allerdings ausgenommen.
Um den Vorgang des finish_redef_table speziell bei einer grossen Anzahl von DML-Operationen zu beschleunigen, empfiehlt es sich zwischendurch eine SYNC Operation durchzuführen, wie im näechsten Beispiel zu sehen ist:

SQL> EXECUTE dbms_redefinition.sync_interim_table('SCOTT', 'ADMIN_EMP', 'INT_ADMIN_EMP');
PL/SQL procedure successfully completed.

SQL> EXECUTE dbms_redefinition.finish_redef_table('SCOTT', 'ADMIN_EMP', 'INT_ADMIN_EMP');
PL/SQL procedure successfully completed.

Es können auch einzelne Partitionen einer partitionierten Tabelle redefiniert werden. Dazu benutzt man als Interimsobjekt eine Hilfstabelle mit den entsprechenden Eigenschaften. Das Vorgehen ist ansonsten vergleichbar mit dem in Beispiel 1. Ausnahmen sind die Prozedure copy_dependent_objects, die nicht genutzt werden kann und lokale Indizes. Falls lokale Indizes existieren, muss manuell ein (lokaler) Index auf den entsprechenden Spalten der Interimstabelle erzeugt werden.

Beispiel 2 zeigt das Einschalten von Komprimierung einer einzelnen Partition. Als Grundlage nehmen wir die Tabelle ORDERS und werden der Partition P_2006_JAN online die Eigenschaft COMPRESS FOR ALL OPERATIONS hinzufügen. Folgende Aufrufe zeigen einen mögliche Ablauf:

CREATE TABLE orders_part 
(order_id NUMBER(12) NOT NULL, 
order_date DATE NOT NULL, 
order_mode VARCHAR2(8), 
order_status VARCHAR(1)) 
COMPRESS FOR ALL OPERATIONS
/

EXECUTE dbms_redefinition.start_redef_table('COMP','ORDERS','ORDERS_PART',part_name=>'P_2006_JAN');
/* Optionales Sync */
EXECUTE dbms_redefinition.sync_interim_table('COMP', 'ORDERS','ORDERS_PART', 'P_2006_JAN');
/* Beenden */
EXECUTE dbms_redefinition.finish_redef_table('COMP', 'ORDERS','ORDERS_PART', 'P_2006_JAN');

Danach kann die Interimstabelle wie im Beispiel 1 einfach gelöscht werden. Globale Indizes wie hier der Primary Key Index sind nach der Redefinition allerdings im Status UNUSABLE.
Für beide Beispiele gilt das Folgende: Möchte man die Redefinition abbrechen, kann man dies mit abort_redef_table Prozedur durchführen. Diese löscht die temporären Logs und Tabellen, die für den Redefinition Prozess benötigt wurden.
Nicht vergessen sollte man, dass ich der gesamte Ablauf generell durch Einschalten von Parallel Query bzw. Parallel DML beschleunigen lässt z.B. mit folgenden Kommandos:

ALTER SESSION force parallel dml parallel ;
ALTER SESSION force parallel query parallel ;

Es gibt sicher eine grosse Anzahl von Anwendungsfällen für die Neudefinition von Tabellen. Dieser Tipp soll das allgemeine Vorgehen zeigen und zum Nachahmen anregen.
Mehr zu weiteren Themen rund um die Oracle Datenbank demnächst.

Zurück zur Community-Seite