玉面飞龙的BLOG

Online Table Redefinition

December 27th, 2007 | Categories: Boring | Tags:

11g 新功能文档上有一段:

1.2.9.6 Minimize Dependent PL/SQL Recompilation After Online Table Redefinition
This feature minimizes the need to recompile dependent PL/SQL packages after an online table redefinition.
If the redefinition does not logically affect the PL/SQL packages, recompilation is not needed.
This optimization is on by default.

This feature reduces the time and effort to manually recompile dependent PL/SQL after an online table redefinition.
This also includes views, synonyms, and other table dependent objects (with the exception of triggers)
that are not logically affected by the redefinition.

意思是说在finish_redef_table后，不会invalidate视图，同义词等其他依赖的对象。弱化了DDL的耦合性。

这确实是个改进。因为在10g中invalidated public syonym可能会导致finish_redef_table失败。

看在10g中的测试:
[coolcode lang=”sql” linenum=”no”]
alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
set pagesize 100 linesize 150
col object_name format a30
SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;

OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——- ——————- ——————-
BINZHANG VALID 2007-12-27 00:19:35 SYNONYM
BINZHANG VALID 2007-12-27 00:18:35 TABLE

SQL> exec dbms_redefinition.can_redef_table ( USER, ‘BINZHANG’, DBMS_REDEFINITION.CONS_USE_PK) ;

PL/SQL procedure successfully completed.

SQL> CREATE TABLE BINZHANG_TMP (
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1));

Table created.

SQL> CREATE UNIQUE INDEX BINZHANGUNIDX_TMP ON BINZHANG_TMP (OBJECT_ID ) ;

Index created.

SQL> alter table BINZHANG_TMP add primary key(object_id);

Table altered.

SQL> exec dbms_redefinition.START_REDEF_TABLE ( USER, ‘BINZHANG’, ‘BINZHANG_TMP’, NULL, DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.

SQL> select count(*) from BINZHANG_TMP;
COUNT(*)
———-
13427

SQL> exec dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’);
PL/SQL procedure successfully completed.

SQL> conn / as sysdba
Connected.
SQL> select count(*) from binzhang;
COUNT(*)
———-
13427

SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
Session altered.

SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——- ——————- ——————-
BINZHANG VALID 2007-12-27 00:28:16 SYNONYM
BINZHANG VALID 2007-12-27 00:27:01 TABLE
[/coolcode]
注意状态变为INVALID 的同义词。当查询很多的时候，finish_redef_table很可能会失败，遇见如下错误。

[coolcode lang=”sql” linenum=”no”]

SQL> exec dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’);
BEGIN dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’); END;
*
ERROR at line 1:
ORA-42012: error occurred while completing the redefinition
ORA-04020: deadlock detected while trying to lock object PUBLIC.BINZHANG
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 76
ORA-06512: at “SYS.DBMS_REDEFINITION”, line 1376
ORA-06512: at line 1
[/coolcode]

在11g中，有了这个不invalidate views, synonyms, and other table dependent objects (with the exception of triggers)的改进。就能够保证finish_redef_table不会因为ORA-04020而失败。

[coolcode lang=”sql” linenum=”no”]

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table binzhang as select * from dba_objects where object_id >0;
Table created.

SQL> alter table binzhang add primary key(object_id);
Table altered.

SQL> exec dbms_redefinition.can_redef_table ( USER, ‘BINZHANG’, DBMS_REDEFINITION.CONS_USE_PK) ;
PL/SQL procedure successfully completed.

SQL> create public synonym binzhang for binzhang.binzhang;
Synonym created.

alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
set pagesize 100 linesize 150
col object_name format a30
Session altered.

SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——————— ——————- ———————————————————
BINZHANG VALID 2007-12-26 23:42:34 SYNONYM
BINZHANG VALID 2007-12-26 23:42:04 TABLE

SQL> create table binzhang_tmp as select * from binzhang where rownum=0;
Table created.

SQL> alter table BINZHANG_TMP add primary key(object_id);
Table altered.

SQL> exec dbms_redefinition.START_REDEF_TABLE ( USER, ‘BINZHANG’, ‘BINZHANG_TMP’, NULL, DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed.

SQL> select count(*) from BINZHANG_TMP;
COUNT(*)
———-
68967

SQL> select count(*) from BINZHANG;
COUNT(*)
———-
68967

SQL> exec dbms_redefinition.finish_redef_table( USER, ‘BINZHANG’, ‘BINZHANG_TMP’);
PL/SQL procedure successfully completed.

SQL> select object_name,status,LAST_DDL_TIME,object_type from dba_objects where object_name=’BINZHANG’;
OBJECT_NAME STATUS LAST_DDL_TIME OBJECT_TYPE
—————————— ——————— ——————- ———————————————————
BINZHANG VALID 2007-12-26 23:42:34 SYNONYM
BINZHANG VALID 2007-12-26 23:41:54 TABLE

[/coolcode]

测试中的public synonym的last ddl time并没有改变。

11g中添加的这些功能增强了DBA维护操作的并发性。