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

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

SQL> 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, name 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)||'name'||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)
Advertisements
This entry was posted in Oracle Availability and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s