Mit dem Redefinition-Package liefert Oracle eine Möglichkeit, im laufenden Betrieb Maintenance-Arbeiten an Tabellen und Indizes durchzuführen. Insbesondere die Partitionionierung von Tabellen bei wachsendem Datenhaushalt läßt sich hiermit einfach umsetzen.
Das Prinzip basiert auf der Erstellung einer neuen Tabelle in der neuen Struktur, dem komfortablen Kopieren alle abhängigen Objekte wie Trigger, Grants, Constraint etc und einer Datensyncronisation basierend auf der Snapshotfunktionalität. Die betroffene Tabelle kann ohne Einschränkungen weiterhin online genutzt werden so das Wartungsintervalle entfallen.
Als Beispiel wählen wird die Partitionierung einer Tabelle bei laufendem Betrieb der Applikation. Dabei soll aus einer nichtpartitionierten Tabelle eine range-partitionierte Tabelle entstehen. Hier die Schritte im einzelnen:
1. Man erstellt eine neue Tabelle im gewünschten Layout.
drop table TABLE_OWNER.NEW_TABLE cascade constraints;
CREATE TABLE TABLE_OWNER.NEW_TABLE
(
ID NUMBER(12) NOT NULL,
APP_ID VARCHAR2(20) ,
APPLICATION VARCHAR2(20) ,
OPERATION VARCHAR2(255) ,
START_TIME TIMESTAMP(6) default current_timestamp not null,
END_TIME TIMESTAMP(6),
)
TABLESPACE TABLE_OWNER_TBSP
partition by range (START_TIME)
(
PARTITION P_200901 VALUES LESS THAN (
TO_DATE(' 2009-01-01 00:00:00',
'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN'))
LOGGING
COMPRESS
TABLESPACE TABLE_OWNER);
--#
--# Wir legen nur eine Parition an, alle
--# weiteren Parttionen lassen wir automatisch
--# vom Partitionierungstool anlegen
--#
begin
for i in 1..24 loop
TABLE_OWNER.psm_pt.crpt( 'NEW_TABLE',
'MONTH',
'TABLE_OWNER',
add_months(
to_date('20090101','yyyymmdd'),
i
)
);
end loop;
end;
/
2. Prüfung, ob sich die Tabelle für die Verwendung zur Redefinition eigent und der Start dieser. In diesem Schritt werden alle Daten der Originaltabelle in die neue Tabelle übertragen und es wird ein Snapshot Log auf der Originaltabelle angelegt.
--#
--# Pruefung
--#
exec Dbms_Redefinition.Can_Redef_Table('TABLE_OWNER', 'SOURCE_TABLE');
--#
--# Beginn
--#
exec DBMS_REDEFINITION.START_REDEF_TABLE('TABLE_OWNER',
'SOURCE_TABLE',
'NEW_TABLE');
3. Optional können neue bzw geänderte Indexe , Trigger oder Constraints angelegt werden. In unserem Fall soll der Primary Key partitioniert werden und muß daher eine neue Spalte bekommen. Anschließend müssen diese Objekte für den folgenden Schritt der Prozedur zur automatischen Erstellung der abhängigen Objekte bekannt gemacht werden. Diese erkennt somit, dass Objekte manuell erstellt wurden und versucht nicht, sie zu kopieren.
--#
--# Manuelle Erstellung der neuen Indexe und Constraints
--#
create unique index TABLE_OWNER.ID_PK_NEW on
TABLE_OWNER.NEW_TABLE (ID, START_TIME)
local;
alter table TABLE_OWNER.NEW_TABLE add constraint
ID_PK_NEW primary key (SERVICE_LOG_ID, START_TIME)
novalidate;
--#
--# Registrierung des manuell erstellten neuen Indexes
--#
exec DBMS_REDEFINITION.register_dependent_object('TABLE_OWNER',
'SOURCE_TABLE',
'NEW_TABLE',
DBMS_REDEFINITION.cons_index,
'TABLE_OWNER',
'ID_PK',
'ID_PK_NEW');
--#
--# Registrierung des manuell erstellten neuen Constraints
--#
exec DBMS_REDEFINITION.register_dependent_object('TABLE_OWNER',
'SOURCE_TABLE',
'NEW_TABLE',
DBMS_REDEFINITION.cons_constraint,
'TABLE_OWNER',
'ID_PK',
'ID_PK_NEW');
4. Die restlichen Objekte werden kopiert. Hierbei gibt es einige zu berücksichtigende Optionen.
--#
--# Beispiel zum Kopieren
--#
set serveroutput on;
declare
v_err PLS_INTEGER;
begin
dbms_redefinition.copy_table_dependents('TABLE_OWNER',
'SOURCE_TABLE',
'NEW_TABLE',
0,
TRUE,
TRUE,
TRUE ,
true,
v_err);
dbms_output.put_line('errors: '||v_err);
end;
/
--#
--# Syntax und Parameter
--#
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
copy_indexes IN PLS_INTEGER := 0,
copy_triggers IN BOOLEAN := TRUE,
copy_constraints IN BOOLEAN := TRUE,
copy_privileges IN BOOLEAN := TRUE,
ignore_errors IN BOOLEAN := FALSE,
num_errors OUT PLS_INTEGER);
Parameters
Parameter Description
uname
The schema name of the tables.
orig_table
The name of the table being redefined.
int_table
The name of the interim table.
copy_indexes
A flag indicating whether to copy the indexes
0 - don't copy any index
dbms_redefinition.cons_orig_params - copy the indexes using the physical parameters of the source indexes
copy_triggers
TRUE implies clone triggers, FALSE implies do nothing
copy_constraints
TRUE implies clone constraints, FALSE implies do nothing
copy_privileges
TRUE implies clone privileges, FALSE implies do nothing
ignore_errors
TRUE implies if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. FALSE implies that the cloning process should stop upon encountering an error.
num_errors
The number of errors that occurred while cloning dependent objects
5. Zum Abschluss wird die neue Tabelle per dbms_stats analysiert und der Redefinitionsprozess wird abgeschlossen. Dabei wird Oracle die in der Zwischenzeit angelaufenen DML's über den Snapshot Log mit der neuen Tabelle syncronisieren und die Tabelle sowie die erstellten abhängigen Objekte umbenennen. Damit ist die Tabelle in die neue Form überführt und die alte Tabelle kann per 'dop' gelöscht werden. Vorsicht: vor dem 'dop' sollte man zur Sicherheit das Ergebnis verifizieren!
--#
--# Analyze
--#
exec dbms_stats.gather_table_stats(ownname => 'TABLE_OWNER',
tabname => 'NEW_TABLE',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,
degree => DBMS_STATS.auto_degree
);
--#
--# Sync des Snapshot Logs und
--# umbennen alle abhängigen Objekte
--#
exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TABLE_OWNER',
'SOURCE_TABLE',
'NEW_TABLE');