点评Oracle11g新特性：在线操作功能增强-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_3156/article/details/82176611

【IT168 Oracle】11g中在线处理功能得到了很大增强，其中包括在线修改表结构，在线创建或重建索引，建立不可见索引，表增加非空字段，在线DDL以及对象依赖性细化等。

在11g以前，DDL操作都要获取对象的排他锁，如果当时无法获取，则会马上报错：

SQL> conn yangtk/yangtk@ytk102已连接。
SQL> create table t (id number);
表已创建。
SQL> insert into t values (1);
已创建 1 行。

在当前会话不提交，保持对t表的锁。然后在另外的session登陆，尝试对t表进行ddl操作：

SQL > conn yangtk / yangtk @ytk102已连接。 SQL > set sqlp ' SQL2> ' SQL2 > truncate table t; truncate table t * 第 1 行出现错误: ORA - 00054 : 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2 > drop table t; drop table t * 第 1 行出现错误: ORA - 00054 : 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2 > alter table t add name varchar2 ( 10 ); alter table t add name varchar2 ( 10 ) * 第 1 行出现错误: ORA - 00054 : 资源正忙, 但指定以 NOWAIT 方式获取资源 SQL2 > select * from v$version; BANNER -- -------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2 . 0.1 . 0 - Prod PL / SQL Release 10.2 . 0.1 . 0 - Production CORE 10.2 . 0.1 . 0 Production TNS for 32 - bit Windows: Version 10.2 . 0.1 . 0 - Production NLSRTL Version 10.2 . 0.1 . 0 - Production

    这就是11g以前的DDL处理方式，在11g中，用户可以为DDL设置一个等待时间，这样DDL在获取不到排他锁时，会根据设置等待相应的时间，如果到时仍然获取不到锁，那么才会报错。

    登陆11g进行同样的处理：
SQL> conn yangtk/yangtk@ora11g已连接。
SQL> create table t (id number);
表已创建。
SQL> insert into t values (1);
已创建 1 行。

    登陆第二个会话，发出DDL操作：

SQL2> conn yangtk/yangtk@ora11g已连接。
SQL2> set timing on
SQL2> truncate table t;
truncate table t
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
已用时间: 00: 00: 00.07

    DDL仍然马上报错，这是由于控制DDL等待时间的初始化参数DDL_LOCK_TIMEOUT默认值是0，下面修改这个默认值：

SQL2> alter session set ddl_lock_timeout = 5;
会话已更改。
已用时间: 00: 00: 00.06
SQL2> truncate table t;
truncate table t
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
已用时间: 00: 00: 05.07

    如果在等待时间内第一个会话进行提交，那么DDL就可以获取到排他锁，从而开始操作。

SQL2> alter session set ddl_lock_timeout = 60;
会话已更改。
已用时间: 00: 00: 00.04
SQL2> truncate table t;
返回会话1进行提交：
SQL> commit;
提交完成。

    会话2在会话1提交后，得到T表的锁，开始TRUNCATE操作：

表被截断。
已用时间: 00: 00: 13.03
SQL2> select * from v$version;
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
已用时间: 00: 00: 00.21

    注意，将DDL_LOCK_TIMEOUT参数设置过大也可能造成潜在的问题，虽然DDL还没有获取到排他锁，不能进行操作，但是它已经在T上增加了锁信息，这会导致后续的DML都会被锁住，直到DDL等待超时或锁住DDL的会话提交或回滚使得DDL顺利完成。

    这对于繁忙的系统可能造成很严重的问题，设置之前需要谨慎考虑。

在11g以前，表中新增一个NOT NULL的字段是十分痛苦的事情，尤其是表很大的情况。不光是执行速度慢，而且由于现有数据长度的变化，很容易造成表中大量的行链接情况。

在11g中，这种情况得到了彻底的改善，Oracle通过在数据字典中记录DEFAULT值，避免了繁重的更新操作，增加非空字段的时间和增加一个可空字段的时间完全一样。

先来看看10g中添加一个包含DEFAULT值的非空字段的情况：

SQL > CONN YANGTK / YANGTK @YTK102已连接。 SQL > CREATE TABLE T AS SELECT * FROM DBA_OBJECTS; 表已创建。 SQL > SELECT COUNT ( * ) FROM T; COUNT ( * ) -- -------- 50357 SQL > SET TIMING ON SQL > ALTER TABLE T ADD NEW_COL CHAR ( 1000 ) DEFAULT ' LARGE COLUMN ' NOT NULL ; 表已更改。已用时间: 00 : 00 : 17.04 SQL > SET TIMING OFF SQL > SELECT SUM (BYTES) / 1024 / 1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME = ' T ' ; SUM (BYTES) / 1024 / 1024 -- ------------------ 72 SQL > SELECT * FROM V$VERSION; BANNER -- -------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2 . 0.1 . 0 - Prod PL / SQL Release 10.2 . 0.1 . 0 - Production CORE 10.2 . 0.1 . 0 Production TNS for 32 - bit Windows: Version 10.2 . 0.1 . 0 - Production NLSRTL Version 10.2 . 0.1 . 0 - Production

在10g中，5万条记录添加一个CHAR(1000)的新增NOT NULL列，需要17秒，而且更新后表的大小为72M。
下面看看11g中的表现：

SQL > CONN YANGTK / yangtk @ORA11G已连接。 SQL > CREATE TABLE T AS SELECT * FROM DBA_OBJECTS; 表已创建。 SQL > SELECT COUNT ( * ) FROM T; COUNT ( * ) -- -------- 68376 SQL > SET TIMING ON SQL > ALTER TABLE T ADD NEW_COL CHAR ( 1000 ) DEFAULT ' LARGE COLUMN ' NOT NULL ; 表已更改。已用时间: 00 : 00 : 00.15 SQL > SET TIMING OFF SQL > SELECT SUM (BYTES) / 1024 / 1024 FROM USER_SEGMENTS WHERE SEGMENT_NAME = ' T ' ; SUM (BYTES) / 1024 / 1024 -- ------------------ 9 SQL > SELECT * FROM V$VERSION; BANNER -- -------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1 . 0.6 . 0 - Production PL / SQL Release 11.1 . 0.6 . 0 - Production CORE 11.1 . 0.6 . 0 Production TNS for Linux: Version 11.1 . 0.6 . 0 - Production NLSRTL Version 11.1 . 0.6 . 0 - Production

    将近7万的数据，添加字段仅用了0.15秒。性能完全没有可比性。从更新后表的大小也可以看到明显的区别。这里只看了执行时间和占用空间两个方面，从生成REDO和UNDO的大小看，前者同样远远超过了后者。

    Oracle采用了何种方法实现了如此的性能提升，说起来也很简单。Oracle11g中，在添加一个包含DEFAULT值的NOT NULL字段，Oracle不会去更新现有的数据，Oracle需要做的不过是将默认值以及对应的表信息、列信息一起存储在一个新增数据字典表ecol$中。

    这张表利用BLOB字段存储ALTER TABLE添加的DEFAULT值：

SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'T';
OBJECT_ID
----------
70482
SQL> SELECT * FROM SYS.ECOL$ WHERE TABOBJ# = 70482;
TABOBJ# COLNUM BINARYDEFVAL
---------- ---------- ----------------------------------------
70482 16 4C4152474520434F4C554D4E

    简单转换一下：

SQL> SET SERVEROUT ON
SQL> DECLARE
2 V_BLOB VARCHAR2(32767) DEFAULT '4C4152474520434F4C554D4E';
3 BEGIN
4 FOR I IN 1..LENGTH(V_BLOB)/2 LOOP
5 DBMS_OUTPUT.PUT(CHR(TO_NUMBER(SUBSTR(V_BLOB, (I - 1) * 2 + 1, 2), 'XXX')));
6 END LOOP;
7 DBMS_OUTPUT.NEW_LINE;
8 END;
9 /
LARGE COLUMN
PL/SQL procedure successfully completed.

    Oracle在读取数据时，发现COLUMN 16列为非空，但是在存储的数据中找不到该列，于是就会从ECOL$中读取该列的默认值。

    这个功能不但提高了添加非空字段的速度，而且节省了大量的磁盘空间。相比之下，在数据字典中查找DEFAULT VALUE的代价小的几乎可以忽略。

在11g之前，Oracle判断依赖性只达到了对象级，也就是说存储过程访问的对象一旦发生了变化，那么Oracle就会将存储过程置为INVALID状态。

Oracle使用这种方法强制存储过程在下次执行之前进行重编译，避免表结构改变后，存储过程没有更新，从而得到错误的结果。

这种方法没有问题，但是有的时候修改往往和关联的存储过程没有关系，但是Oracle并不对这种情况进行判断，这样会造成很多不需要重新编译的存储过程也被置于INVALID状态：

SQL > CREATE TABLE T (ID NUMBER ); 表已创建。 SQL > CREATE OR REPLACE PROCEDURE P_TEST AS 2 BEGIN 3 INSERT INTO T (ID) VALUES ( 1 ); 4 END ; 5 / 过程已创建。 SQL > SELECT OBJECT_NAME , OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = ' P_TEST ' ; OBJECT_NAME OBJECT_TYPE STATUS -- ---------------------------- ------------------- ------- P_TEST PROCEDURE VALID SQL > ALTER TABLE T ADD (NAME VARCHAR2 ( 30 )); 表已更改。 SQL > SELECT OBJECT_NAME , OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = ' P_TEST ' ; OBJECT_NAME OBJECT_TYPE STATUS -- ---------------------------- ------------------- ------- P_TEST PROCEDURE INVALID SQL > SELECT * FROM V$VERSION; BANNER -- -------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2 . 0.1 . 0 - Prod PL / SQL Release 10.2 . 0.1 . 0 - Production CORE 10.2 . 0.1 . 0 Production TNS for 32 - bit Windows: Version 10.2 . 0.1 . 0 - Production NLSRTL Version 10.2 . 0.1 . 0 - Production

上面这个简单的例子就说明了这种情况，对于存储过程P_TEST来说，只是访问了T的ID列，T表新增NAME列，并不会影响存储过程P_TEST。但是在10g及以前版本，Oracle的判断依赖性达不到字段级。

而从11g开始，Oracle细化了依赖性的判断，下面看看在11g中同样的例子：

SQL > CONN YANGTK / yangtk @ORA11G已连接。 SQL > CREATE TABLE T (ID NUMBER ); 表已创建。 SQL > CREATE OR REPLACE PROCEDURE P_TEST AS 2 BEGIN 3 INSERT INTO T (ID) VALUES ( 1 ); 4 END ; 5 / 过程已创建。 SQL > SELECT OBJECT_NAME , OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = ' P_TEST ' ; OBJECT_NAME OBJECT_TYPE STATUS -- ---------------------------- ------------------- ------- P_TEST PROCEDURE VALID SQL > ALTER TABLE T ADD (NAME VARCHAR2 ( 30 )); 表已更改。 SQL > SELECT OBJECT_NAME , OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = ' P_TEST ' ; OBJECT_NAME OBJECT_TYPE STATUS -- ---------------------------- ------------------- ------- P_TEST PROCEDURE VALID SQL > SELECT * FROM V$VERSION; BANNER -- --------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1 . 0.6 . 0 - Production PL / SQL Release 11.1 . 0.6 . 0 - Production CORE 11.1 . 0.6 . 0 Production TNS for Linux: Version 11.1 . 0.6 . 0 - Production NLSRTL Version 11.1 . 0.6 . 0 - Production

添加、删除索引一直是一个比较头痛的问题。不在正式环境中进行添加、删除操作，很难了解索引对执行计划的影响。而在正式环境中添加、删除索引，又很容易影响其他SQL的执行计划，从而导致系统出现性能问题。

Oracle11g新增了INVISIBLE INDEX功能，可以新增一个不可见的索引，或者将目前的索引变为不可见。除非指定了参数，否则优化器不会使用这种状态的索引。

SQL > CREATE TABLE T AS SELECT * FROM DBA_OBJECTS; 表已创建。 SQL > CREATE INDEX IND_T_OWNER ON T (OWNER); 索引已创建。 SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS( USER , ' T ' ) PL / SQL 过程已成功完成。 SQL > SET AUTOT ON EXP SQL > SELECT COUNT ( * ) FROM T WHERE OWNER = ' YANGTK ' ; COUNT ( * ) -- -------- 24 执行计划 -- -------------------------------------------------------- Plan hash value: 225622394 -- ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | -- ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 8 ( 0 ) | 00 : 00 : 01 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | INDEX RANGE SCAN | IND_T_OWNER | 3108 | 18648 | 8 ( 0 ) | 00 : 00 : 01 | -- ----------------------------------------------------------------------------- Predicate Information (identified by operation id): -- ------------------------------------------------- 2 - access("OWNER" = ' YANGTK ' ) SQL > ALTER INDEX IND_T_OWNER INVISIBLE; 索引已更改。 SQL > SELECT COUNT ( * ) FROM T WHERE OWNER = ' YANGTK ' ; COUNT ( * ) -- -------- 24 执行计划 -- -------------------------------------------------------- Plan hash value: 2966233522 -- ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | -- ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 284 ( 1 ) | 00 : 00 : 04 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | TABLE ACCESS FULL | T | 3108 | 18648 | 284 ( 1 ) | 00 : 00 : 04 | -- ------------------------------------------------------------------------- Predicate Information (identified by operation id): -- ------------------------------------------------- 2 - filter("OWNER" = ' YANGTK ' )

如果希望优化器考虑不可见索引，可以在系统级或会话级设置初始化参数：OPTIMIZER_USE_INVISIBLE_INDEXES设置为TRUE：

SQL > SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES NAME TYPE VALUE -- ---------------------------------- ----------- ------------------------------ optimizer_use_invisible_indexes boolean FALSE SQL > ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE; 会话已更改。 SQL > SELECT COUNT ( * ) FROM T WHERE OWNER = ' YANGTK ' ; COUNT ( * ) -- -------- 24 执行计划 -- -------------------------------------------------------- Plan hash value: 225622394 -- ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost ( % CPU) | Time | -- ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 8 ( 0 ) | 00 : 00 : 01 | | 1 | SORT AGGREGATE | | 1 | 6 | | | |* 2 | INDEX RANGE SCAN | IND_T_OWNER | 3108 | 18648 | 8 ( 0 ) | 00 : 00 : 01 | -- ----------------------------------------------------------------------------- Predicate Information (identified by operation id): -- ------------------------------------------------- 2 - access("OWNER" = ' YANGTK ' )

可以通过USER_INDEXES的新增字段VISIBILITY字段来查看索引的可见性：

SQL> SET AUTOT OFF
SQL> CREATE INDEX IND_T_NAME ON T (OBJECT_NAME);
索引已创建。
SQL> SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE TABLE_NAME = 'T';
INDEX_NAME VISIBILIT
------------------------------ ---------
IND_T_NAME VISIBLE
IND_T_OWNER INVISIBLE

利用不可见索引可以在不影响其他会话的情况下查看索引建立后的效果。在删除索引之前可以将索引先至于不可见状态，这样一旦发现索引不应该被删除，索引的恢复将会十分迅速。

在11g以前，Oracle提供的只读功能只到了数据库级和表空间级：

SQL > CONN / AS SYSDBA Connected. SQL > SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL > STARTUP OPEN READ ONLY ORACLE instance started. Total System Global Area 267825152 bytes Fixed Size 1299316 bytes Variable Size 176163980 bytes Database Buffers 88080384 bytes Redo Buffers 2281472 bytes Database mounted. Database opened. SQL > INSERT INTO YANGTK.T VALUES ( 2 ); INSERT INTO YANGTK.T VALUES ( 2 ) * ERROR at line 1 : ORA - 16000 : database open for read - only access

如果数据库级设置了只读打开，数据库中任何对象都无法进行修改。

SQL > SHUTDOWN Database closed. Database dismounted. ORACLE instance shut down. SQL > STARTUP ORACLE instance started. Total System Global Area 267825152 bytes Fixed Size 1299316 bytes Variable Size 176163980 bytes Database Buffers 88080384 bytes Redo Buffers 2281472 bytes Database mounted. Database opened. SQL > INSERT INTO YANGTK.T VALUES ( 2 ); 1 row created. SQL > COMMIT ; Commit complete. SQL > SELECT TABLESPACE_NAME FROM ALL_TABLES WHERE OWNER = ' YANGTK ' AND TABLE_NAME = ' T ' ; TABLESPACE_NAME -- ---------------------------- YANGTK SQL > ALTER TABLESPACE YANGTK READ ONLY ; Tablespace altered. SQL > INSERT INTO YANGTK.T VALUES ( 3 ); INSERT INTO YANGTK.T VALUES ( 3 ) * ERROR at line 1 : ORA - 00372 : file 5 cannot be modified at this time ORA - 01110 : data file 5 : ' /data/oracle/oradata/ora11g/ORA11G/datafile/o1_mf_yangtk_3d3kn7k4_.dbf '

设置表空间只读，那么表空间中所有的对象都无法被修改。也就是说，如果在11g之前，想要实现对某张表的只读，只有建立一个表空间，将表放到该表空间中，再将表空间至于只读状态。
不但操作麻烦，而且如果需要添加新的只读表，而不打算新建立一个表空间的话，需要先将只读表空间置为可写，然后将新的表放进去，再将其置为只读，而在操作期间，表空间中原有的对象可能会被修改。

从11g开始，Oracle终于提供了表级的只读设置了，显现方法也很简单，一个ALTER TABLE READ ONLY语句就搞定了：

SQL > ALTER TABLESPACE YANGTK READ WRITE; Tablespace altered. SQL > CONN YANGTK / yangtk Connected. SQL > INSERT INTO T VALUES ( 3 ); 1 row created. SQL > ALTER TABLE T READ ONLY ; Table altered. SQL > INSERT INTO T VALUES ( 4 ); INSERT INTO T VALUES ( 4 ) * ERROR at line 1 : ORA - 12081 : update operation not allowed on table "YANGTK"."T"

将表置于只读状态，并非只是禁止DML语句，包含FOR UPDATE的查询语句，已经会引起表中数据发生变化的DDL语句都会被禁止。而不影响表中数据的DDL则可以正常执行：

SQL > SELECT * FROM T; ID -- -------- 1 2 3 SQL > SELECT * FROM T FOR UPDATE ; SELECT * FROM T FOR UPDATE * ERROR at line 1 : ORA - 12081 : update operation not allowed on table "YANGTK"."T" SQL > ALTER TABLE T ADD NAME VARCHAR2 ( 30 ); ALTER TABLE T ADD NAME VARCHAR2 ( 30 ) * ERROR at line 1 : ORA - 12081 : update operation not allowed on table "YANGTK"."T" SQL > ALTER TABLE T MODIFY ID NOT NULL ; ALTER TABLE T MODIFY ID NOT NULL * ERROR at line 1 : ORA - 12081 : update operation not allowed on table "YANGTK"."T" SQL > ALTER TABLE T ALLOCATE EXTENT; Table altered. SQL > ALTER TABLE T CACHE; Table altered.

Oracle的在线重定义功能就是利用了物化视图的功能，通过物化视图日志的记录功能，来同步目标表和基表的数据。因此，在11g以前，建立了物化视图日志的表是无法进行在线重定义操作的：

SQL > CREATE TABLE T AS SELECT ROWNUM ID, A. * FROM DBA_OBJECTS A; 表已创建。 SQL > CREATE INDEX IND_T_NAME ON T ( OBJECT_NAME ); 索引已创建。 SQL > ALTER TABLE T ADD CONSTRAINTS PK_T PRIMARY KEY (ID); 表已更改。 SQL > BEGIN 2 DBMS_REDEFINITION.CAN_REDEF_TABLE( USER , ' T ' ); 3 END ; 4 / PL / SQL 过程已成功完成。 SQL > CREATE MATERIALIZED VIEW LOG ON T; 实体化视图日志已创建。 SQL > BEGIN 2 DBMS_REDEFINITION.CAN_REDEF_TABLE( USER , ' T ' ); 3 END ; 4 / BEGIN * 第 1 行出现错误: ORA - 12091 : 不能联机重新定义具有实体化视图的表 "YANGTK"."T" ORA - 06512 : 在 "SYS.DBMS_REDEFINITION", line 137 ORA - 06512 : 在 "SYS.DBMS_REDEFINITION", line 1478 ORA - 06512 : 在 line 2 SQL > SELECT * FROM V$VERSION; BANNER -- -------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2 . 0.1 . 0 - Prod PL / SQL Release 10.2 . 0.1 . 0 - Production CORE 10.2 . 0.1 . 0 Production TNS for 32 - bit Windows: Version 10.2 . 0.1 . 0 - Production NLSRTL Version 10.2 . 0.1 . 0 - Production

在11g中，Oracle会利用现有的物化视图日志来完成在线重定义的功能。同时，物化视图日志也可以作为表的从属信息同步到目标上。不过在同步完成后，需要物化视图执行完全刷新。

SQL > CREATE TABLE T AS SELECT ROWNUM ID, A. * FROM USER_OBJECTS A; 表已创建。 SQL > CREATE INDEX IND_T_NAME ON T ( OBJECT_NAME ); 索引已创建。 SQL > ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID); 表已更改。 SQL > CREATE MATERIALIZED VIEW LOG ON T; 实体化视图日志已创建。 SQL > BEGIN 2 DBMS_REDEFINITION.CAN_REDEF_TABLE( USER , ' T ' ); 3 END ; 4 / PL / SQL 过程已成功完成。 SQL > SELECT * FROM V$VERSION; BANNER -- ----------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1 . 0.6 . 0 - Production PL / SQL Release 11.1 . 0.6 . 0 - Production CORE 11.1 . 0.6 . 0 Production TNS for Linux: Version 11.1 . 0.6 . 0 - Production NLSRTL Version 11.1 . 0.6 . 0 - Production

最后简单给出11g中包含物化视图日志的在线重定义操作步骤：

SQL > CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS SELECT * FROM T; 实体化视图已创建。 SQL > DELETE T WHERE ID = 1 ; 已删除 1 行。 SQL > COMMIT ; 提交完成。 SQL > SELECT COUNT ( * ) FROM T; COUNT ( * ) -- -------- 23 SQL > SELECT COUNT ( * ) FROM MV_T; COUNT ( * ) -- -------- 24 SQL > CREATE TABLE T_INTER PARTITION BY HASH (ID) 2 PARTITIONS 4 3 AS SELECT ROWNUM ID, A. * FROM USER_OBJECTS A WHERE 1 = 2 ; 表已创建。 SQL > BEGIN 2 DBMS_REDEFINITION.START_REDEF_TABLE( USER , ' T ' , ' T_INTER ' ); 3 END ; 4 / PL / SQL 过程已成功完成。 SQL > VAR V_NUM NUMBER SQL > BEGIN 2 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( USER , ' T ' , ' T_INTER ' , COPY_MVLOG => TRUE, NUM_ERRORS = > :V_NUM); 3 END ; 4 / PL / SQL 过程已成功完成。 SQL > PRINT :V_NUM V_NUM -- -------- 0 SQL > SELECT COUNT ( * ) FROM T; COUNT ( * ) -- -------- 23 SQL > SELECT COUNT ( * ) FROM T_INTER; COUNT ( * ) -- -------- 23 SQL > DELETE T WHERE ID = 2 ; 已删除 1 行。 SQL > COMMIT ; 提交完成。 SQL > BEGIN 2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE( USER , ' T ' , ' T_INTER ' ); 3 END ; 4 / PL / SQL 过程已成功完成。 SQL > SELECT COUNT ( * ) FROM T_INTER; COUNT ( * ) -- -------- 22 SQL > BEGIN 2 DBMS_REDEFINITION.FINISH_REDEF_TABLE( USER , ' T ' , ' T_INTER ' ); 3 END ; 4 / PL / SQL 过程已成功完成。 SQL > SELECT TABLE_NAME, PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ' T ' ; TABLE_NAME PARTITION_NAME -- ---------------------------- ------------------------------ T SYS_P45 T SYS_P46 T SYS_P47 T SYS_P48 SQL > EXEC DBMS_MVIEW.REFRESH( ' MV_T ' ) BEGIN DBMS_MVIEW.REFRESH( ' MV_T ' ); END ; * 第 1 行出现错误: ORA - 12034 : "YANGTK"."T" 上的实体化视图日志比上次刷新后的内容新 ORA - 06512 : 在 "SYS.DBMS_SNAPSHOT", line 2537 ORA - 06512 : 在 "SYS.DBMS_SNAPSHOT", line 2743 ORA - 06512 : 在 "SYS.DBMS_SNAPSHOT", line 2712 ORA - 06512 : 在 line 1 SQL > EXEC DBMS_MVIEW.REFRESH( ' MV_T ' , ' C ' ) PL / SQL 过程已成功完成。 SQL > DELETE T WHERE ID = 3 ; 已删除 1 行。 SQL > COMMIT ; 提交完成。 SQL > EXEC DBMS_MVIEW.REFRESH( ' MV_T ' ) PL / SQL 过程已成功完成。 SQL > SELECT * FROM MV_T WHERE ID = 3 ; 未选定行

11g在线重定义对物化视图日志进行了支持，同时COPY_TABLE_DEPENDENTS过程也添加了对物化视图日志拷贝的功能。

Oracle 11g加强了ONLINE REBUILD索引功能，减少了ONLINE REBUILD索引过程中对DML操作的阻塞。下面看看10g中和11g在线重建索引的差别，首先登陆10g：
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已创建。
SQL> DELETE T WHERE ID = 1;
已删除 1 行。

    会话1中建立表和索引后，删除一条记录，且不提交。

SQL> SET SQLP 'SQL2> '
SQL2> ALTER INDEX IND_T_NAME REBUILD;
ALTER INDEX IND_T_NAME REBUILD
*第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
SQL2> ALTER INDEX IND_T_NAME REBUILD ONLINE;

    在会话2上尝试重建索引。由于无法马上获取锁，直接REBUILD INDEX会报错，而REBUILD INDEX ONLINE则会处于等待状态，直到获取表的锁。

    这时在会话1上查询锁信息和会话2的等待情况：

SQL > SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1 ; SID -- -------- 148 SQL > SELECT SID FROM V$SESSION WHERE USERNAME = ' YANGTK ' ; SID -- -------- 144 148 SQL > SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN ( 144 , 148 ); SID TY ID1 CTIME BLOCK -- -------- -- ---------- ---------- ---------- 144 DL 52896 360 0 144 DL 52896 360 0 148 TM 52896 417 1 144 TM 52896 360 0 144 TM 52899 359 0 148 TX 65575 417 0 已选择6行。 SQL > COL EVENT FORMAT A40 SQL > COL P1TEXT FORMAT A20 SQL > SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 144 ; EVENT P1TEXT P1 SECONDS_IN_WAIT -- -------------------------------------- -------------------- ---------- --------------- enq: TM - contention name | mode 1414332420 749

会话2处于等待锁的状态中，下面提交会话1的修改，随后马上执行一个DELETE操作。为了避免COMMIT和DELETE操作间隔时间太大，在文本编辑器中，将两个命令编辑好，拷贝到剪贴板，然后一起拷贝到SQLPLUS中：

SQL > COMMIT ; 提交完成。 SQL > DELETE T WHERE ID = 2 ; 已删除 1 行。 SQL > SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN ( 144 , 148 ); SID TY ID1 CTIME BLOCK -- -------- -- ---------- ---------- ---------- 144 DL 52896 828 0 144 DL 52896 828 0 148 TM 52896 26 1 144 TM 52896 26 0 144 TM 52899 827 0 144 TS 7 26 0 144 TX 262169 26 0 148 TX 589847 26 0 已选择8行。 SQL > SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 144 ; EVENT P1TEXT P1 SECONDS_IN_WAIT -- -------------------------------------- -------------------- ---------- --------------- enq: TM - contention name | mode 1414332420 38

    再次查询，发现会话2仍然在等待锁信息，不过等待时间已经重新计算了。观察V$LOCK视图的信息可以发现会话2仍然是被锁住，但是状态已经和第一次不相同了。根据文档的描述，ONLINE REBUILD在开始和完成的时候都会去获取锁，现在应该是等待完成时的锁。

SQL> COMMIT;
提交完成。
SQL> DELETE T WHERE ID = 3;
已删除 1 行。

    在会话1再次执行COMMIT加DELETE操作，发现会话2的REBUILD操作已经完成。
索引已更改。

SQL2> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

    再来看11g中的情况：

SQL> CONN YANGTK/yangtk@ORA11G已连接。
SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;
表已创建。
SQL> CREATE INDEX IND_T_NAME ON T(OBJECT_NAME);
索引已创建。
SQL> DELETE T WHERE ID = 1;
已删除 1 行。

    模仿上面的例子，在会话1中保持锁。会话2执行ONLINE REBUILD INDEX处于等待锁的状态。

SQL2 > CONN YANGTK / yangtk @ORA11G已连接。 SQL2 > ALTER INDEX IND_T_NAME REBUILD ONLINE; 在会话1中查询会话2的等待情况和锁占有情况： SQL > SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1 ; SID -- -------- 131 SQL > SELECT SID FROM V$SESSION WHERE USERNAME = ' YANGTK ' ; SID -- -------- 131 149 SQL > SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN ( 131 , 149 ); SID TY ID1 CTIME BLOCK -- -------- -- ---------- ---------- ---------- 149 AE 99 50 0 149 OD 70713 47 0 131 AE 99 78 0 149 DL 70712 47 0 149 DL 70712 47 0 149 OD 70712 47 0 149 TX 589851 47 0 149 TM 70712 47 0 149 TM 70715 47 0 131 TM 70712 65 0 149 TX 262171 47 0 131 TX 589851 65 1 已选择12行。 SQL > SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 149 ; EVENT P1TEXT P1 SECONDS_IN_WAIT -- -------------------------------------- -------------------- ---------- --------------- enq: TX - row lock contention name | mode 1415053316 77

在11g中，会话2等待的是行级锁信息，尝试在会话1中提交并马上删除：

SQL > COMMIT ; 提交完成。 SQL > DELETE T WHERE ID = 2 ; 已删除 1 行。 SQL > SELECT SID, TYPE, ID1, CTIME, BLOCK FROM V$LOCK WHERE SID IN ( 131 , 149 ); SID TY ID1 CTIME BLOCK -- -------- -- ---------- ---------- ---------- 149 AE 99 110 0 149 OD 70713 107 0 131 AE 99 138 0 149 DL 70712 107 0 149 DL 70712 107 0 149 OD 70712 107 0 149 TX 196637 10 0 149 TM 70712 107 0 149 TM 70715 107 0 131 TM 70712 10 0 149 TX 262171 107 0 131 TX 196637 10 1 已选择12行。 SQL > SELECT EVENT, P1TEXT, P1, SECONDS_IN_WAIT FROM V$SESSION_WAIT WHERE SID = 149 ; EVENT P1TEXT P1 SECONDS_IN_WAIT -- -------------------------------------- -------------------- ---------- --------------- enq: TX - row lock contention name | mode 1415053316 15

    最后在会话1再次提交：

SQL> COMMIT;
提交完成。
SQL> DELETE T WHERE ID = 3;
已删除 1 行。

    这是会话2的REBUILD操作完成：

索引已更改。
SQL2> SELECT * FROM V$VERSION;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

    看上去，10g和11g的ONLINE REBUILD操作并没有什么不同，在创建索引开始和结束的时候都要获取到表的锁。不过二者获取的锁信息不同，也就是说二者对于后续DML的影响并不相同。

    在11g中，ONLINE REBUILD操作获取的锁不会阻塞后续DML操作，而11g以前的版本，在ONLINE REBUILD索引过程中，获取到锁的一个短暂时刻会阻止DML操作。

    下面演示一下二者的区别，首先在11g中，一个会话运行ONLINE REBUILD，另一个会话同时运行对表的DML操作，第三个会话查询V$LOCK视图，检查ONLINE REBUILD操作是否会阻塞DML操作。
    会话1：

SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;
SID
----------
131
SQL> CREATE INDEX IND_T_ID ON T(ID);
索引已创建。
SQL> ALTER INDEX IND_T_NAME REBUILD ONLINE;
索引已更改。

    会话2：

SQL2> BEGIN
2 FOR I IN 1..100000 LOOP
3 UPDATE T SET ID = ID WHERE ID = 1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。

    会话3：

SQL> CONN YANGTK/yangtk@ORA11G已连接。
SQL> SET SQLP 'SQL3> '
SQL3> SET SERVEROUT ON
SQL3> DECLARE
2 V_NUM NUMBER;
3 V_TOTAL NUMBER DEFAULT 0;
4 BEGIN
5 FOR I IN 1..1000 LOOP
6 SELECT SUM(BLOCK) INTO V_NUM FROM V$LOCK WHERE SID = 154;
7 V_TOTAL := V_TOTAL + NVL(V_NUM, 0);
8 END LOOP;
9 DBMS_OUTPUT.PUT_LINE(V_TOTAL);
10 END;
11 /
0
PL/SQL 过程已成功完成。

     确保会话1的REBUILD ONLINE与会话2、会话3的存储过程几乎同时运行。可以看到在整个REBUILD索引过程中，没有阻塞DML操作。

    下面看看同样的操作在10g下执行的结果。

    会话1：

SQL> CONN YANGTK/YANGTK@YTK102已连接。
SQL> SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1;
SID
----------
144
SQL> CREATE INDEX IND_T_ID ON T(ID);
索引已创建。
SQL> ALTER INDEX IND_T_NAME REBUILD ONLINE;
索引已更改。

    会话2：

SQL2> CONN YANGTK/YANGTK@YTK102已连接。
SQL2> BEGIN
2 FOR I IN 1..100000 LOOP
3 UPDATE T SET ID = ID WHERE ID = 1;
4 COMMIT;
5 END LOOP;
6 END;
7 /
PL/SQL 过程已成功完成。

    会话3：

SQL3> CONN YANGTK/YANGTK@YTK102已连接。
SQL3> SET SERVEROUT ON
SQL3> DECLARE
2 V_NUM NUMBER;
3 V_TOTAL NUMBER DEFAULT 0;
4 BEGIN
5 FOR I IN 1..1000 LOOP
6 SELECT SUM(BLOCK) INTO V_NUM FROM V$LOCK WHERE SID = 144;
7 V_TOTAL := V_TOTAL + NVL(V_NUM, 0);
8 END LOOP;
9 DBMS_OUTPUT.PUT_LINE(V_TOTAL);
10 END;
11 /
6
PL/SQL 过程已成功完成。
SQL3> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

    从上面的结果不难看出，11g以前版本在ONLINE REBUILD的时候会对DML操作短暂的阻塞，而11g彻底消除了ONLINE REBUILD对DML的影响。