DBMS_REDEFINITION

Convert non-partition table to partition table using Oracle redefinition.

Goals

1.       To convert the non-partition table to partition table

2.      No/minimal application downtime

3.      Dependent objects should also get created

4.      Option to convert current non-primary key global index to local indexes

5.       

I’ve used scott schema to redefine a table. Scott will need special privileges for using DBMS_REDEFINITION Package

SQL> grant create any table, alter any table, drop any table,lock any table, select any table, create any trigger, create any index to scott;

Check the existing table & it’s associated attributes

desc EMP

Name                                      Null?    Type
----------------------------------------- -------- -------------
EMPNO                                     NOT NULL NUMBER
NAME                                               VARCHAR2(100)
DEPTNO                                    NOT NULL NUMBER

SQL> select table_name, index_name from user_indexes

/

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
EMP                            IDX_DEPTNO
EMP                            SYS_C004149

SQL> select  constraint_name, constraint_type, table_name 
from user_constraints
/

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------SYS_C004149                    P EMP
SYS_C004150                    C EMP

Now perform the following steps to redefine the table

STEP 1

Check to make sure that the table can use the online redefinition feature

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','EMP');

PL/SQL procedure successfully completed.

STEP 2

Create a interim partitioned table  (this table will eventually become actual table)

SQL> create table tmp_emp (empno number, ename varchar2(100), 
deptno number) 
partition by list (deptno) 
partition p_10 values (10), 
partition p_20 values (20), 
partition p_30 values (30),
partition p_40 values (40),
partition p_50 values (50),
partition p_60 values (60))
/

I’m creating a local index on the interim table & not copying as part of dependent objects, which is a global index.

SQL> create index idx_deptno_lcl on tmp_emp(deptno) local
/

SQL> desc tmp_emp

Name                                      Null?    Type
----------------------------------------- -------- --------------
EMPNO                                              NUMBER
NAME                                               VARCHAR2(100)
DEPTNO                                             NUMBER

SQL> select table_name, partition_name high_value from user_tab_partitions
/

TABLE_NAME                     HIGH_VALUE
------------------------------ ------------------------------
TMP_EMP                        P_60
TMP_EMP                        P_50
TMP_EMP                        P_40
TMP_EMP                        P_30
TMP_EMP                        P_20
TMP_EMP                        P_10

6 rows selected.

STEP 3

Start the online redefinition process

SQL>EXEC DBMS_REDEFINITION.START_REDEF_TABLE ('SCOTT','EMP','TMP_EMP');
PL/SQL procedure successfully completed.

STEP 4

Copy dependent objects
We will use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

SQL> VARIABLE v_num NUMBER;
SQL> EXEC DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('SCOTT','EMP','TMP_EMP', 0,TRUE,TRUE,TRUE,FALSE,:v_num,FALSE);

PL/SQL procedure successfully completed.

SQL> PRINT  v_num
 V_NUM
----------         
0

Check the associated attributes for interim table

SQL>  select table_name, index_name from user_indexes
/

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
TMP_EMP                        IDX_DEPTNO_LCL
TMP_EMP                        TMP$$_SYS_C0041490
EMP                            IDX_DEPTNO
EMP                            SYS_C004149

SQL> select  constraint_name, constraint_type, table_name from user_constraints
/

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
TMP$$_SYS_C0041370             P TMP_EMP
SYS_C004137                    P EMP
SYS_C004138                    C EMPTMP
$$_SYS_C0041380             C TMP_EMP

STEP 5

Resync the table. This will copy the initial data from the EMP to TMP_EMP table.

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','EMP','TMP_EMP');

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

COUNT(*)
----------        
91

SQL> select count(*) from tmp_emp
/

 COUNT(*)

----------        

91

Now add some rows in the original table EMP.

SQL> set serveroutput on 
declarev1 varchar2(100);
begin
for i in 101..10000
loop
v1:='insert into emp 
values ('||i||','||chr(39)||'ename'||i||chr(39)||',60)';
execute immediate v1;
end loop;
exception
when others then
dbms_output.put_line(substr(sqlerrm,1,50));
end;
/

SQL> select count(*) from emp
/

 COUNT(*)
----------      
9991

SQL>  select count(*) from tmp_emp
/

COUNT(*)
----------        
91

Again resync the table to make sure that the data is correctly getting copied to interim table

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT','EMP','TMP_EMP');

PL/SQL procedure successfully completed.

SQL>  select count(*) from tmp_emp
/

COUNT(*)
----------      
9991

STEP 6

Finish the online redefinition. Here the redefinition process will be over and interim table will become original table.

SQL> select count(*) from tmp_emp

/

 COUNT(*)
----------
      9991

Just to simulate the load on original table, I’ve added another set of rows from one session & started the finish procedure from another session. Till the point, the insert is not complete & commit is not issued, finish procedure will wait.  So ideally one should execute this procedure, when load on the system is low.

declare
v1 varchar2(100);
begin
for i in 10001..20000
loop
v1:='insert into emp 
values ('||i||','||chr(39)||'name'||i||chr(39)||',60)';
execute immediate v1;
end loop;
exception
when others then
dbms_output.put_line(substr(sqlerrm,1,50));
end;
/

SQL> commit;
Commit complete.

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','TMP_EMP');

SQL>  select count(*) from tmp_emp;

 COUNT(*)
----------    
19991

SQL> desc emp 

Name                                      Null?    Type 
----------------------------------------- -------- ------------- 
EMPNO                                              NUMBER 
NAME                                               VARCHAR2(100) 
DEPTNO                                             NUMBER

SQL> select table_name, index_name from user_indexes
/

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
TMP_EMP                        IDX_DEPTNO
TMP_EMP                        SYS_C004149
EMP                            IDX_DEPTNO_LCL
EMP                            TMP$$_SYS_C0041490

SQL> select  constraint_name, constraint_type, table_name from user_constraints;

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
SYS_C004149                    P EMP
TMP$$_SYS_C0041490             P TMP_EMP
SYS_C004150                    C EMPTMP
$$_SYS_C0041500             C TMP_EMP

STEP 7

Cross verify the table constraints

SQL> insert into emp values (11,'dsdsd',60);

insert into emp values (11,'dsdsd',60)*ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C004149) violated

SQL>  insert into emp values (NULL,'dsdsd',60); 

insert into emp values (NULL,'dsdsd',60)  *ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")

SQL> insert into emp values (11111,'lllll',NULL);

insert into emp values (11111,'lllll',NULL)  *ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."DEPTNO")

STEP 8

Rename the constraints to required a meaningful name

SQL> ALTER INDEX TMP$$_SYS_C0041540 rename to pk_empno; 

If we want to abort the redefinition process in between, we can use abort procedure

SQL> EXEC DBMS_REDEFINITION.ABORT_REDEF_TABLE ('SCOTT','EMP','TMP_EMP',NULL);

Limitations to the redefinition

1.       Cannot belong to SYS or SYSTEM Schema.

2.      The table to be redefined online should not be using User-defined data types

3.      Should not be a clustered table.

4.      Should not have MV Logs or MVs defined

5.      Table owner cannot be changed, as part of re-definition

6.      Additional space requirement for the interim table (same as original table)

Error / Solutions

SQL> insert into scott.emp values(123,'Demo','Clerk',321,sysdate,2000,10,10);

insert into scott.emp values(123,'Demo','Clerk',321,sysdate,2000,10,10)

                  *

ERROR at line 1:

ORA-04098: trigger 'SCOTT.TEST_EMP' is invalid and failed re-validation

Solution:-

SQL>  select TRIGGER_NAME,TABLE_OWNER,TABLE_NAME from user_triggers

SQL> drop trigger TEST_EMP;

Trigger dropped.

SQL> select * from user_triggers;