Oracle 12c database has introduced enhancement to DBMS_REDEFINITION for specifying a a lock timeout (in seconds) which will allow FINISH_REDEF_TABLE to acquire an exclusive lock for swapping the source and interim tables.If timeout expires, then operation exits. This will help to avoid cancellation by user or indefinite wait. This does not put our session in queue which will be shown by below example
1. Let's test this on EMP table. I have created table in my schema and copied records.
CREATE TABLE "AMITBANS"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX) tablespace users;
2. Lets hash partition the table on empno. Note that I have excluded primary key constraint/index as we will copy it using dbms_redefinition
CREATE TABLE "AMITBANS"."INT_EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) tablespace users partition by hash(empno) partitions 4;
3. Lets check if dbms_redefinition will work on this table
set serverout on exec DBMS_REDEFINITION.CAN_REDEF_TABLE('AMITBANS','EMP',DBMS_REDEFINITION.CONS_USE_PK);
There are no errors, so we are safe to proceed.
4. Next Start the redefinition
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP',OPTIONS_FLAG=>dbms_redefinition.cons_use_pk); END; /
5. Copy dependent objects. (Automatically create any triggers, indexes, materialized view logs, grants, and constraints on AMITBANS.INT_EMP.)
DECLARE l_num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP',COPY_INDEXES=> DBMS_REDEFINITION.CONS_ORIG_PARAMS, COPY_TRIGGERS=>TRUE,COPY_CONSTRAINTS=> TRUE, COPY_PRIVILEGES=>TRUE, IGNORE_ERRORS =>TRUE,NUM_ERRORS=> l_num_errors,COPY_STATISTICS=>TRUE); END; /
We need to check if there were any errors while copying constraints. Use below query
select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;
6. We can sync any new inserts by issuing following command.
BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP'); END; /
7. Let's do finish redefinition, but before that we will do two insert in emp table from different session without committing transaction.
We will do it in following order to show that finish_redef_table needs to have no active dml on table.
session 1: Insert record 1
insert into emp values(8000,'HENRY','ANALYST',7698,sysdate,1500,null,10);
Session 2: Issue finish_redef_table to swap tables
BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME =>'AMITBANS', ORIG_TABLE=>'EMP',INT_TABLE=>'INT_EMP',dml_lock_timeout=>300); END; /
Session 3: Open one more session and do insert
insert into emp values(8001,'MICHAEL','ANALYST',7698,sysdate,1500,null,10);
If you now commit transaction in session 1, redefinition (session 2) will wait (to acquire exclusive lock) for session 3 to commit .This shows that it is not in a queue and needs all active transactions to finish. When we commit session 3, finish_redef_table succeeds.