Rolling Back Online Table Redefinition

 Imagine a situation where we have changed the structure of a large table using the DBMS_REDEFINITION package and converted it to a partitioned table. After completing the redefinition process, we realize that the new structure has made operations on this table slower. We now want to revert the table back to its original non-partitioned structure. What is the solution?

One of the improvements added to the DBMS_REDEFINITION package in Oracle 12c is the ability to roll back changes made during a redefinition operation. This is done using the enable_rollback parameter in the DBMS_REDEFINITION package.

Below is a scenario demonstrating how to use this feature:

SQL> CREATE TABLE mytbl (id NUMBER(12) primary key,name varchar2(19),last_name varchar2(19),date_ date );

Table created

SQL> CREATE TABLE mytbl_temp (id NUMBER(12) primary key,name varchar2(19),last_name varchar2(19),date_ date )

  2  PARTITION BY RANGE (date_)

  3  INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’))

  4  ( PARTITION p1 VALUES LESS THAN (TO_DATE(‘1-1-2000’, ‘DD-MM-YYYY’)));

Table created

SQL> EXEC DBMS_REDEFINITION.can_redef_table(‘USEF’, ‘MYTBL’);

PL/SQL procedure successfully completed

SQL> EXEC DBMS_REDEFINITION.start_redef_table(‘USEF’, ‘MYTBL’, ‘MYTBL_TEMP’,enable_rollback => TRUE);

PL/SQL procedure successfully completed

SQL> EXEC DBMS_REDEFINITION.sync_interim_table(‘USEF’, ‘MYTBL’, ‘MYTBL_TEMP’);

PL/SQL procedure successfully completed

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(‘USEF’, ‘MYTBL’, ‘MYTBL_TEMP’);

PL/SQL procedure successfully completed

After the operation is finished, the table becomes partitioned:

SQL> select p.PARTITIONED from user_tables p where table_name=’MYTBL’;

YES

Assume we insert some data into the table:

SQL> insert into mytbl values(1,’vahid’,’usefzadeh’,TO_DATE(‘1-1-2001’, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into mytbl values(2,’vahid’,’usefzadeh’,TO_DATE(‘1-1-2005’, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

At this point, we conclude that the table performs better with its original structure. Therefore, we can use the new rollback capability introduced in Oracle 12c:

SQL> EXEC DBMS_REDEFINITION.rollback(‘USEF’, ‘MYTBL’, ‘MYTBL_TEMP’);

PL/SQL procedure successfully completed

Now, by running the following command, we can see that the table is no longer partitioned:

SQL> select p.PARTITIONED from user_tables p where table_name=’MYTBL’;

PARTITIONED

———–

NO

SQL> select count(*) from MYTBL;

  COUNT(*)

———-

         2

Written by Vahid Yousefzadeh

I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com