mysql已有表建分区在线重定义_Oracle 12c 新特性 --- dbms_redefinition.finish_redef_table指定在线重定义的锁定时间...

概念https://www.cndba.cn/leo1990/article/2164

https://www.cndba.cn/leo1990/article/2164

Now you can specify a lock timeout in number of seconds during which time FINISH_REDEF_TABLE attempts to acquire an exclusive lock for swapping the source and interim tables and, if timeout expires, the operation exits.

现在，您可以在数秒内指定锁定超时，在此期间完成对源和临时表交换的独占锁定，如果超时过期，则操作退出。

This feature increases the flexibility of FINISH_REDEF_TABLE to exit after waiting a user-specified number of seconds so that the user does not wait indefinitely or needs to force exit of the online redefinition session.

这个特性增加了FINISH_REDEF_TABLE在等待用户指定的秒数后退出的灵活性，这样用户就不会无限期地等待或者需要强制退出在线重新定义会话。

Oracle 12c数据库已经引入了对DBMS_REDEFINITION的增强，用于指定一个锁定超时(以秒为单位)，这将允许FINISH_REDEF_TABLE获得对源和临时表交换的独占锁。如果超时过期，则操作退出。这将有助于避免用户取消或无限期等待。这并没有将我们的会话放在队列中，下面的示例将显示该会话

https://www.cndba.cn/leo1990/article/2164

1. 创建测试表test

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 24 00:50:56 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> conn [email protected]

Connected.

SQL> CREATE TABLE cndba

("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) tablesp 2 ace users; 3 4 5 6 7 8 9 10 11

Table created.

2 创建测试表并在empno分区。注意，我已经排除了主键约束/索引，因为我们将使用dbms_redefinition复制它

SQL> CREATE TABLE int_cndba

("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; 2 3 4 5 6 7 8 9 10 11

Table created.

3. 让我们检查dbms_redefinition是否在这个表上工作

SQL> set serverout on

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','CNDBA',DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

There are no errors, so we are safe to proceed.

4. 接下来开始重新定义

SQL> BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE(UNAME =>'TEST', ORIG_TABLE=>'CNDBA',INT_TABLE=>'INT_CNDBA',OPTIONS_FLAG=>dbms_redefinition.cons_use_pk);

END;

/ 2 3 4

PL/SQL procedure successfully completed.

5. 复制依赖对象。(自动创建任何触发器、索引、物化视图日志、授予和对 TESTS.INT_CNDBA 的约束)。

SQL> DECLARE

l_num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(UNAME =>'TEST', ORIG_TABLE=>'CNDBA',INT_TABLE=>'INT_CNDBA',COPY_INDEXES=> DBMS_REDEFINITION.CONS_ORIG_PARAMS,

COPY_TRIGGERS=>TRUE,COPY_CONSTRAINTS=> TRUE, COPY_PRIVILEGES= 2 3 4 5 >TRUE, IGNORE_ERRORS =>TRUE,NUM_ERRORS=> l_num_errors,COPY_STATISTICS=>TRUE);

END;

/ 6 7

PL/SQL procedure successfully completed.

在复制约束时，我们需要检查是否存在错误。使用以下查询

SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

no rows selected

6. 我们可以通过发出以下命令来同步任何新的插入。

SQL> BEGIN

DBMS_REDEFINITION.SYNC_INTERIM_TABLE(UNAME =>'TEST', ORIG_TABLE=>'CNDBA',INT_TABLE=>'INT_CNDBA');

END;

/

2 3 4

PL/SQL procedure successfully completed.

7. 让我们完成重新定义，但在此之前，我们将从不同的会话中执行插入CNDBA表，而不提交事务。

我们将按照以下顺序执行，以显示finish_redef_table需要在表上没有活动的dml。

session 1: Insert record 1

SQL> insert into CNDBA values(8000,'HENRY','ANALYST',7698,sysdate,1500,null,10);

1 row created.

Session 2: Issue finish_redef_table to swap tables

SQL> BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME =>'TEST', ORIG_TABLE=>'CNDBA',INT_TABLE=>'INT_CNDBA',dml_lock_timeout=>60);

END;

/ 2 3 4

BEGIN

*

ERROR at line 1:

ORA-42012: error occurred while completing the redefinition

ORA-42042: time out in acquiring DML lock during online redefinition

ORA-06512: at "SYS.DBMS_REDEFINITION", line 105

ORA-06512: at "SYS.DBMS_REDEFINITION", line 3520

ORA-06512: at line 2

redefinition(会话2)将等待(获得独占锁)会话1提交。这表明它不在队列中，需要活动事务完成,如果您现在在会话1中提交事务，finish_redef_table成功。

--此时 session 1 提交 finish_redef_table成功。

SQL> commit;

Commit complete.

SQL> BEGIN

DBMS_REDEFINITION.FINISH_REDEF_TABLE(UNAME =>'TEST', ORIG_TABLE=>'CNDBA',INT_TABLE=>'INT_CNDBA',dml_lock_timeout=>60);

END;

/ 2 3 4

PL/SQL procedure successfully completed.

8 在线重定义成功。

SQL> set lines 300

SQL> select *from cndba;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

8003 MICHAEL ANALYST 7698 24-AUG-17 1500 10

8000 HENRY ANALYST 7698 24-AUG-17 1500 10

8001 MICHAEL ANALYST 7698 24-AUG-17 1500 10

8002 HENRY ANALYST 7698 24-AUG-17 1500 10

SQL> select * from INT_CNDBA;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

8000 HENRY ANALYST 7698 24-AUG-17 1500 10

8001 MICHAEL ANALYST 7698 24-AUG-17 1500 10

8002 HENRY ANALYST 7698 24-AUG-17 1500 10

8003 MICHAEL ANALYST 7698 24-AUG-17 1500 10

https://www.cndba.cn/leo1990/article/2164

版权声明：本文为博主原创文章，未经博主允许不得转载。