12c:Specifying lock timeout for finish_redef_table

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.

Tags: 12cdbms_redefinitionfinish_redef_table