Online Table Redefinition and Partitioning using DBMS_REDEFINITION in Oracle


I will explain Online Table Partitioning using the DBMS_REDEFINITION in Oracle database in this article.

oracle partition



Read previous article if you don’t know What is the Partitioning.



It is very important not to take downtime or to take a minimum downtime when a critical operation is performed in the production database.  Oracle offers technologies that minimize downtime for these critical cases. Here is one of them  DBMS_REDEFINITION package introduced with Oracle 9i.

You can perform lots of operations with DBMS_REDEFINITION package on tables without downtime or minimum downtime as follows.

  • Partition a Table
  • Create index on Heap Table
  • Add or drop column on Table
  • Modify table storage characteristic
  • Reorganize table


In the production database, it is very important and vital to perform the above operations which are very frequently used for large tables. That’s why I fully recommend using DBMS_REDEFINITION package.


So how to perform and what is Oracle doing in the background when perform Partitioning Online Table with DBMS_REDEFINITION ?

The following image outlines the Table Partitioning event with the DBMS_REDEFINITION package.





  1. Oracle creates interim table ( with new structure ) and transfers the main table’s base data to this materalized view.
  2. Creates Materalized view log and directs online transactions to main table to here.
  3. All data in the main table are transfered to the interim table ( Partitioned table ) via Start Redef Table.
  4. All changes on main table in the Materalized view log are applied to the interim table via Sync Interim table.
  5. Main table is replaced with interim table with Finish redef table.




Let’s go to make an example about Online table partitioning with DBMS_REDEFINITION .

Our main table and its description is like following.

Fatura—> billing

Müşteri—> Customer

Borc—-> Debt

Fatura Tarihi—> Bill Date



SQL> select * from msdeveci.fatura;



Create interim table ( Partitioned table ) like following. You need to do all changes for new tables on this interim table, Interim table will replace with main table After this operation is done.

 PARTITION Fatura201301 VALUES LESS THAN(TO_DATE('01/01/2013','DD/MM/YYYY')),
 PARTITION Fatura201302 VALUES LESS THAN(TO_DATE('01/02/2013','DD/MM/YYYY')),
 PARTITION Fatura201303 VALUES LESS THAN(TO_DATE('01/03/2013','DD/MM/YYYY')),
 PARTITION Fatura201304 VALUES LESS THAN(TO_DATE('01/04/2013','DD/MM/YYYY')),
 PARTITION Fatura201305 VALUES LESS THAN(TO_DATE('01/05/2013','DD/MM/YYYY')),
 PARTITION Fatura201306 VALUES LESS THAN(TO_DATE('01/06/2013','DD/MM/YYYY')),
 PARTITION Fatura201307 VALUES LESS THAN(TO_DATE('01/07/2013','DD/MM/YYYY')));

Before starting Redefinition, check whether Main table and interim table are suitable for this process by running the CAN_REDEF_TABLE procedure of the DBMS_REDEFINITION package.

The following script is executed to check the table with primary key.


— If There is no primary key on Table, execute the following script to check with ROWNUM.


START_REDEF_TABLE procedure will transfer all base data from main table to interim table via Primary key.

COPY_TABLE_DEPENDENTS will copy all table dependents from main table to interim table.


Execute following script to transfer base data of main table to the interim table and copy all dependents on main table to the interim table.


 redefinition_errors PLS_INTEGER := 0;

 uname => 'MSDEVECI'
 ,orig_table => 'fatura'
 ,int_table => 'fatura_old'
 ,col_mapping => NULL

 uname => 'MSDEVECI'
 ,orig_table => 'fatura'
 ,int_table => 'fatura_old'
 ,copy_indexes => 0
 ,copy_triggers => TRUE
 ,copy_constraints => TRUE
 ,copy_privileges => TRUE
 ,ignore_errors => TRUE
 ,num_errors => redefinition_errors
 ,copy_statistics => FALSE
 ,copy_mvlog => FALSE);
IF (redefinition_errors > 0) THEN
 DBMS_OUTPUT.PUT_LINE('>>> AUDIT_COUNTER_RD to AUDIT_COUNTER failed: ' || TO_CHAR(redefinition_errors));

After executing the above script,  run the FINISH_REDEF_TABLE procedure of the DBMS_REDEFINITION package to finish the Redefinition process as follows.

 uname => 'MSDEVECI'
 ,orig_table => 'fatura'
 ,int_table => 'fatura_old'



After running the above script,  Redefinition process is completed. Name of main table is replaced with interim table’s name. Thus, the main table ( unpartitioned table ) is transformed into a partitioned table




In order for the FINISH_REDEF_TABLE procedure not to take too long during the Redefinition of very large tables, perform  synchronization between interim table and main table  by running the SYNC_INTERIM_TABLE procedure of the DBMS_REDEFINITION package. The FINISH_REDEF_TABLE procedure will be completed in a shorter time when you execute SYNC_INTERIM_TABLE procedure. SYNC_INTERIM_TABLE procedure is as follows.



Perform Redefinition process with following sequence.




You can check whether table partitioned or not like following.

select * from dba_tab_partitions where table_name='FATURA';



You can query any bill from specific partition like following.

 select * from fatura partition(Fatura201306) where musteri_id=57052;



When we display the execution plan of the above query, we can see that related data is queried from the Partition as follows.




If you want to learn more details about Partitioning Types ( Range , List, Hash, Interval .. ), read the following post.

Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database


Do you want to learn Oracle Database for Beginners, then read the following articles.

 924 views last month,  2 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected]. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!