Online Table Redefinition and Partitioning using DBMS_REDEFINITION in Oracle

Hi,

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

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.

CREATE TABLE MSDEVECI.FATURA_OLD
 (
 MUSTERI_ID NUMBER PRIMARY KEY,
 ADI VARCHAR2(50 BYTE),
 BORC NUMBER,
 FATURA_TARIHI DATE
 )
 PARTITION BY RANGE(FATURA_TARIHI)(
 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.

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('MSDEVECI', 'fatura',1);

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

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('MSDEVECI', 'fatura',2);

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.

SQL> DECLARE
 redefinition_errors PLS_INTEGER := 0;
 BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE (
 uname => 'MSDEVECI'
 ,orig_table => 'fatura'
 ,int_table => 'fatura_old'
 ,col_mapping => NULL
 ,options_flag => DBMS_REDEFINITION.CONS_USE_PK
 );


DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (
 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));
 END IF;
END;
 /

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

begin
DBMS_REDEFINITION.FINISH_REDEF_TABLE (
 uname => 'MSDEVECI'
 ,orig_table => 'fatura'
 ,int_table => 'fatura_old'
 );
 END;

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.

BEGIN
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MSDEVECI', 'fatura', 'fatura_old');
 END;
 /

Perform Redefinition process with following sequence.

CAN_REDEF_TABLE —> START_REDEF_TABLE ->> COPY_TABLE_DEPENDENTS  ——>   SYNC_INTERIM_TABLE –           —–> FINISH_REDEF_TABLE

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.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/