Oracle online redefinition — maintenance during 7×24 operation
The redefinition package gives you a way to carry out maintenance work on tables and indexes while the system stays online. Partitioning a table whose data volume has outgrown its original design is a particularly good fit.
Overview
The principle is to create a new table in the new structure, copy all dependent objects such as triggers, grants and constraints, and synchronise the data using the materialised view log mechanism. The table concerned remains fully available online, which removes the need for a maintenance window.
Example: partitioning a table
As an example we partition a table while the application keeps running, turning a non-partitioned table into a range-partitioned one. Here are the individual steps.
1. Create a new table with the desired 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. Check whether the table is eligible for redefinition and start the process. In this step all data from the original table is transferred into the new table and a materialised view log is created on the original table.
--#
--# 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. Optionally, new or modified indexes, triggers or constraints can be created. In our case the primary key is to be partitioned and therefore needs an additional column. These objects then have to be registered for the next step so that the procedure which creates dependent objects automatically recognises that they were created manually and does not try to copy them.
--#
--# 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. The remaining objects are copied. There are a few options worth considering here.
--#
--# 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. Finally the new table is analysed with dbms_stats and the redefinition process
is completed. Oracle synchronises the DML that has accumulated in the meantime through the
materialised view log, then renames the table and the dependent objects that were created. The
table has now been converted to its new form and the old table can be dropped. A word of
caution: verify the result before you drop anything.
--#
--# 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');