【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操作：
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ --> 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的影响。
1
