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
----------------------------------------- -------- -------------
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
------------------------------ ------------------------------
EMP IDX_DEPTNO
EMP SYS_C004149
SQL> select
constraint_name, constraint_type, table_name
from user_constraints
/
from user_constraints
/
CONSTRAINT_NAME
C TABLE_NAME
------------------------------ - ------------------------------SYS_C004149 P EMP
SYS_C004150 C EMP
------------------------------ - ------------------------------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))
/
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
----------------------------------------- -------- --------------
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.
------------------------------ ------------------------------
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.
PL/SQL procedure successfully completed.
STEP 4
Copy dependent objects
We will use DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
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> 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
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
------------------------------ ------------------------------
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
------------------------------ - ------------------------------
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.
PL/SQL procedure successfully completed.
SQL> select count(*)
from emp;
COUNT(*)
----------
91
----------
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;
/
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
----------
9991
SQL> select
count(*) from tmp_emp
/
/
COUNT(*)
----------
91
----------
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.
PL/SQL procedure successfully completed.
SQL> select
count(*) from tmp_emp
/
/
COUNT(*)
----------
9991
----------
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
----------
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;
/
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.
Commit complete.
SQL> EXEC
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT','EMP','TMP_EMP');
SQL> select
count(*) from tmp_emp;
COUNT(*)
----------
19991
----------
19991
SQL> desc emp
Name
Null? Type
----------------------------------------- -------- -------------
EMPNO NUMBER
NAME VARCHAR2(100)
DEPTNO NUMBER
----------------------------------------- -------- -------------
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
------------------------------ ------------------------------
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
------------------------------ - ------------------------------
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
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")
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;
