REDEFINING TABLES ONLINE – ORACLE 11G
from http://avdeo.com/2012/10/28/redefining-tables-online-oracle-11g/
Introduction:
One of the many challenges that we face in production environment is make changes to big tables.
If you consider a case of any OLTP systems, its easy to have tables whose size is beyond 10G.
This again depends on the nature of the database and kind of transactions happening on the table.
So lets consider a case where you have a big table which is also a very hot table having very high number of transactions / sec and something that continues 24X7.
Its difficult to take downtime on such tables and making DDL changes to such tables could be a nightmare.
Oracle has a great feature introudcued since Oracle 9i, but many DBAs doesnt seem to be aware of this feature – Online table redefiniation.
Online table redefinition allows you to make DDL changes to the table definition and requires very little downtime (less than a minute).
Techinically its not the same table that gets modified, but its another copy of the same table which has the required modification made.
You might question if we are making a new copy of the table we can as well use CTAS (Create Table as Select) and make the required changes.
But its not just about creating new object and copying the data. Online redefinition does lot more than that.
I will briefly explain you the features of online redefinition, followed by process and then we will straight way get to the examples which will help you to understand better.
Features – What it can do:
Online table redefinition enables you to:
- Modify the storage parameters of a table or cluster
- Move a table or cluster to a different tablespace
- Add, modify, or drop one or more columns in a table or cluster
- Add or drop partitioning support (non-clustered tables only)
- Change partition structure
- Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
- Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
- Re-create a table or cluster to reduce fragmentation
Process – How its done:
To briefly explain the process, it involves following steps
1) Check if the table can be refined by running DBMS_REDEFINITION.CAN_REDEF_TABLE procedure.
We have following restrictions on redefining the table
- One cannot redefine Materialized Views (MViews) and tables with MViews or MView Logs defined on them.
- One cannot redefine Temporary and Clustered Tables
- One cannot redefine tables with BFILE, LONG or LONG RAW columns
- One cannot redefine tables belonging to SYS or SYSTEM
- One cannot redefine Object tables
- Table redefinition cannot be done in NOLOGGING mode (watch out for heavy archiving)
2) Create a new table with all of the desired logical and physical attributes.
If you want to change non-partition table to partition, you can create a new partition table. Structure of the table should be exactly the way you want to convert to.
3) Start redefinition process using DBMS_REDEFINITION.START_REDEF_TABLE procedure.
Be careful before running this command. If you must know, this command will start populating new table from the data in old table.
So if your old table is very big, then you need to have same amount of space available in the tablespace where new table is created.
Also, this command might take very long time if the size is big, so make sure you don’t have any disconnection in between.
If needed you can enable parallel before starting redefinition using following commands
alter session force parallel dml parallel degree-of-parallelism;
alter session force parallel query parallel degree-of-parallelism;
4) Sync new table on regular basis till cut off time
You should use DBMS_REDEFINITION.SYNC_INTERIM_TABLE and keep new table in sync with changes that happens on current production table.
This will reduce the cut off time. Cut off time if when you are going to point everything to new table and services will start writing to new table.
The more you keep new table and current production table in sync, lesser will be cut off time and downtime.
5) Copy dependent objects (such as triggers, indexes, grants, and constraints)
Copy dependent objects (such as triggers, indexes, materialized view logs, grants, and constraints) and statistics from production table to new table.
You should make sure that all dependents are copied.
You can do this manually by creating each dependent object or you can do it automatically using DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
6) Complete redefinition process by using DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure
This will complete the redefinition process. This needs exclusive lock on production table which you want to redefine.
So you need to arrange for short downtime. However, FINISH_REDEF_TABLE will wait for all pending DML to commit before completing the redefinition.
Example:
Lets take an example:
We have a table T as shown below.
We have a primary key on OBJECT_ID column.
We have a public synonym for table T.
SQL>desc T
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL>
Currently this is not a partitioned table
SQL>select table_name, partitioned from user_tables where table_name = 'T';
TABLE_NAME PAR
------------------------------ ---
T NO
Lets try to convert this into partition table.
You can check the meaning of every parameter supplied to below procedures at –http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_redefi.htm
Step 1) Check if the table can be refined by running DBMS_REDEFINITION.CAN_REDEF_TABLE procedure.
SQL>set serveroutput on
SQL>EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user,'T',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.
SQL>
If the table is not a candidate for online redefinition, an error message is raised.
Step 2) Create a new intrim table with all of the desired logical and physical attributes.
For table T lets try to partition by CREATED which is a date column. I am planning to partition by year so we can get 10 partitions
SQL>select to_char(CREATED,'YYYY') from T group by to_char(CREATED,'YYYY');
TO_C
----
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
10 rows selected.
CREATE TABLE "T_INTRIM"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(128),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1),
"NAMESPACE" NUMBER,
"EDITION_NAME" VARCHAR2(30),
CONSTRAINT "T_PK_INTRIM_01" PRIMARY KEY ("OBJECT_ID")
)
PARTITION BY RANGE(CREATED)
(
PARTITION T_2003 VALUES LESS THAN (TO_DATE('01-JAN-2004','DD-MON-YYYY')),
PARTITION T_2004 VALUES LESS THAN (TO_DATE('01-JAN-2005','DD-MON-YYYY')),
PARTITION T_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY')),
PARTITION T_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')),
PARTITION T_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION T_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')),
PARTITION T_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')),
PARTITION T_2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')),
PARTITION T_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')),
PARTITION T_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY'))
);
3) Start redefinition process using DBMS_REDEFINITION.START_REDEF_TABLE procedure.
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => user,
orig_table => 'T',
int_table => 'T_INTRIM',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
);
END;
/
After this both table should have near about same amount of record
SQL>select count(1) from T_INTRIM;
COUNT(1)
----------
61536
SQL>select count(1) from T;
COUNT(1)
----------
61536
SQL>
If you have continuous inserts going on your original table than you might have little more records in original table than intrim table.
4) Sync new table on regular basis till cut off time
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => user,
orig_table => 'T',
int_table => 'T_INTRIM'
);
END;
/
The way this sync works is, online redefinition will automatically create a MLOG table on original table.
In any of the above step we didn’t create any MLOG table on table T.
But if you check now, you will see MLOG table created automatically.
SQL>select log_table from user_snapshot_logs where master = 'T';
LOG_TABLE
------------------------------
MLOG$_T
This is required for syncing changed made to table T.
5) Copy dependent objects (such as triggers, indexes, grants, and constraints)
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => user,
orig_table => 'T',
int_table => 'T_INTRIM',
copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => TRUE,
num_errors => num_errors);
END;
/
PL/SQL procedure successfully completed.
Before we finish online redefinition you can check if table is partition and data is distributed to all partitions
SQL>select table_name, partitioned from user_tables where table_name in ('T','T_INTRIM');
TABLE_NAME PAR
------------------------------ ---
T NO
T_INTRIM YES
SQL>select to_char(created,'YYYY'), count(1) from T_INTRIM group by to_char(created,'YYYY');
TO_C COUNT(1)
---- ----------
2003 7902
2005 1820
2009 2742
2010 6765
2008 2612
2007 1016
2011 10474
2004 756
2012 23474
2006 3975
10 rows selected.
Once we finish redefinition table T will become partition table and T_INTRIM will become non-partition table.
For this it needs exclusive lock.
6) Complete redefinition process by using DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_INTRIM');
END;
/
PL/SQL procedure successfully completed.
SQL>select table_name, partitioned from user_tables where table_name in ('T','T_INTRIM');
TABLE_NAME PAR
------------------------------ ---
T YES
T_INTRIM NO
SQL>
so now table T is partitioned table.
References:
http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_redefi.htm
