9i新特性之——在线表格重定义研究在（转）

在Oracle9i出现之前，你只能通过MOVE或导出和导入的方式来进行表格重定义，因此表格重定义的过程可能相当漫长或者说是一个离线过程，在此期间应用程序对该表的操作将失败，特别是大型的分区表上的操作（上次coolyl）就问到了这个问题，我们当时是建议他用move partition的办法解决，但是move可能很耗时并可能引起全局索引的失效（不过9i已经有了update global indexes）。除了这个，如果用exp，我们也不能保证exp的时候该表的数据没有改变（除非单用户），而imp更是一个漫长的过程。[@more@]

为了解决这个问题，Oracle9i在其DBMS_REDEFINITION软件包中引入了在线表格重定义功能。这个特性对24/7Oracle数据库来说非常重要，这是由于DBA现在可以在保持表格的可更新性的同时重新组织表格的结构。

然而，在线表格重定义也不是完美无缺的。下面列出了Oracle9i重定义过程的部分限制。
·你必须有足以维护两份表格拷贝的空间。
·你不能更改主键栏。
·表格必须有主键。
·必须在同一个大纲中进行表格重定义。
·在重定义操作完成之前，你不能对新加栏加以NOT NULL约束。
·表格不能包含LONG、BFILE以及用户类型（UDT）。
·不能重定义链表（clustered tables）。
·不能在SYS和SYSTEM大纲中重定义表格。
·不能用具体化视图日志（materialized view logs）来重定义表格；不能重定义含有具体化视图的表格。
·不能在重定义过程中进行横向分集（horizontal subsetting）。

基本步骤如下

第一步：利用dbms_redefinition.can_redef_table过程检查该表是否能被在线重定义。

第二步：创建一个与原表类似的空表结构，用于重定义该表，这里叫做是中间表

第三步：用dbms_redefinition.start_redef_table procedure定义重构开始
该过程可以输入如下参数信息
用户的名称
原表的名称
中间表的名称
需要同步改变的列的名称

这个过程将回自动执行如下操作：
1、插入所有行从原有表到中间表
2、创建MLOG$_EMP快照与快照日志，临时存储DML语句直到完成。

第四步：与原表一致，在中间表上面创建约束，索引，触发器
与原表一致（如果需要），中间表的对象权限被授予给别的对象
任何中间表上的外键约束将被禁止

第五步：用dbms_redefinition.finish_redef_table过程完成表的最终重定义
该过程将自动完成
应用快照日志中的DML到中间表
互换原表与中间表的名字，包括所有可能出现的数据字典
但是需要注意的是，并不对换约束，索引，触发器的名称，这些需要手工修改

第六步：删除中间表、

第七步：如果是920以上，可以利用ALTER TABLE ... RENAME CONSTRAINT ...语句来修改约束名称，如果以下版本，就只有删除并重建了，当然，如果约束名称并不重要，也就无所谓了

第八步：如果重组织失败，那么你就必须采取特殊的步骤来让它重新开始。由于重定义过程需要创建表格的快照，因此为了重新开始这一过程，你必须调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来释放快照。

详细过程
1、我们创建一个单独的测试用户用来测试整个过程

SQL> create user mytest identified by mytest;
User created
SQL> grant connect,resource to mytest;
Grant succeeded

SQL> connect mytest/mytest;
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
Connected as mytest

2、首先，我们创建一个原表，假定是我们需要在线重新定义的表，由于工作需要，我们需要把该表（不是分区表）重新定义为一个分区表，而且不能影响应用程序的运行。

SQL> create table test(a int,b int) tablespace users;
Table created
模拟一个原表

declare i integer;
begin
for i in 1..100 loop
insert into test values(i,100-i);
end loop;
commit;
end;
插入100条模拟数据

create or replace trigger tr_test
before insert or update or delete on test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update audit_test set c=c+1;
commit;
end;
在上面创建一个触发器，模拟原表可能会有的触发器，这个触发器的作用就是如果有任何DML操作，将在audit_test中增加1。

Audit_test现在的数据是：
SQL> select c from audit_test;
100

检查触发器是否工作正常
SQL> insert into test values(101,0);
1 row inserted
SQL> commit;

SQL> select c from audit_test;
101
可以看到，触发器工作正常。

3、检查该表是否能重定义
SQL> exec dbms_redefinition.can_redef_table('MYTEST', 'TEST');

begin dbms_redefinition.can_redef_table('MYTEST', 'TEST'); end;

ORA-12089: cannot online redefine table "MYTEST"."TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1
可以看到，如果重新定义，需要主键，所以我们增加该表的主键

我们定义主键
SQL> alter table test add constraint pk_test_id primary key(a);
Table altered

SQL> exec dbms_redefinition.can_redef_table('MYTEST', 'TEST');
PL/SQL procedure successfully completed
现在发现，我们可以定义该表了

4、创建我们需要重新定义的中间表，这个是一个分区表，以后我们将把原表的所有数据在线转移到该表上来。

SQL>create table int_test
(a int,b int)
partition by range(a)
(partition p10 values less than(50),
partition p20 values less than(100),
partition p30 values less than(150),
partition p40 values less than(200));
Table created

然后我们检查所有对象，所有对象共9个

SQL> select object_name,object_type,status,object_id,data_object_id from user_objects;
AUDIT_TEST TABLE VALID 33542 33542
INT_TEST TABLE PARTITION VALID 33558 33558
INT_TEST TABLE PARTITION VALID 33559 33559
INT_TEST TABLE PARTITION VALID 33560 33560
INT_TEST TABLE PARTITION VALID 33561 33561
INT_TEST TABLE VALID 33557
PK_TEST_ID INDEX VALID 33551 33551
TEST TABLE VALID 33541 33541
TR_TEST TRIGGER VALID 33543
9 rows selected

5、执行在线重新定义开始
SQL> execute
DBMS_REDEFINITION.START_REDEF_TABLE('MYTEST','TEST','INT_TEST');

begin SYS.DBMS_REDEFINITION.START_REDEF_TABLE('MYTEST','TEST','INT_TEST'); end;

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1

这里存储过程返回一个错误，说没有足够的权限，的确是这样，经查，如果要执行这个存储过程，起码需要如下权限：
* CREATE ANY TABLE
* ALTER ANY TABLE
* DROP ANY TABLE
* LOCK ANY TABLE
* SELECT ANY TABLE

我们可以临时授予DBA权限给用户，完之后取消掉，或转移到其他有权限的用户执行这个操作：

SQL> CONNECT SYSTEM/CHEN
Connected to Oracle9i Enterprise Edition Release 9.2.0.4.0
Connected as SYSTEM
SQL> execute SYS.DBMS_REDEFINITION.START_REDEF_TABLE('MYTEST','TEST','INT_TEST');
PL/SQL procedure successfully completed

这里可以看到，我们用system执行成功，那么这个过程到底两个表执行了那些语句呢？我们可以通过如下的语句来验证一下

SQL> select sql_text from v$sqlarea where sql_text like '%TEST%';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT CURRENT$."A",CURRENT$."B" FROM (SELECT "TEST"."A" "A","TEST"."B" "B" FROM
select * from "MYTEST"."TEST"
ALTER TRIGGER MYTEST.TR_TEST COMPILE DEBUG
SELECT /*+ NO_MERGE NO_MERGE(LL$) ROWID(MAS$) ORDERED USE_NL(MAS$) NO_INDEX(MAS
DELETE FROM "MYTEST"."INT_TEST" SNAP$ WHERE "A" = :1
truncate table "MYTEST"."INT_TEST" purge snapshot log
delete from "MYTEST"."MLOG$_TEST" where snaptime$$ <= :1
INSERT INTO "MYTEST"."INT_TEST" ("A","B") VALUES (:1,:2)
select sql_text from v$sqlarea where sql_text like '%TEST%'
UPDATE "MYTEST"."INT_TEST" SET "A" = :1,"B" = :2 WHERE "A" = :1
SELECT "TEST"."A" "A","TEST"."B" "B" FROM "MYTEST"."TEST" "TEST"
begin dbms_redefinition.can_redef_table('MYTEST', 'TEST'); end;
select count(*) from snap$ where (vname, sowner) in (('TEST','MYTEST'))
comment on table "MYTEST"."RUPD$_TEST" is 'temporary updatable snapshot log'
begin DBMS_REDEFINITION.START_REDEF_TABLE('MYTEST','TEST','INT_TEST'); end;
comment on table "MYTEST"."MLOG$_TEST" is 'snapshot log for master table MYTEST.
INSERT INTO "MYTEST"."INT_TEST"("A","B") SELECT "TEST"."A","TEST"."B" FROM "MYTE
INSERT /*+ APPEND */ INTO "MYTEST"."INT_TEST"("A","B") SELECT "TEST"."A","TEST".
update "MYTEST"."MLOG$_TEST" set snaptime$$ = :1 where snaptime$$ > to_date('21
SELECT DISTINCT LOG$."A" FROM (SELECT MLOG$."A" FROM "MYTEST"."MLOG$_TEST" MLOG$

20 rows selected

以上的语句我不再解释，整个内部过程我也不再解释，只是需要大家明白，其实这里的主要需要了解的是，就是把原表的数据给中间表复制了一份

我们再检查所有对象

SQL> select object_name, object_type, status, object_id, data_object_id from user_objects order by 4;
TEST TABLE VALID 33541 33541
AUDIT_TEST TABLE VALID 33542 33542
TR_TEST TRIGGER VALID 33543
PK_TEST_ID INDEX VALID 33551 33551
INT_TEST TABLE VALID 33557
INT_TEST TABLE PARTITION VALID 33558 33564
INT_TEST TABLE PARTITION VALID 33559 33565
INT_TEST TABLE PARTITION VALID 33560 33566
INT_TEST TABLE PARTITION VALID 33561 33567
MLOG$_TEST TABLE VALID 33562 33562
RUPD$_TEST TABLE VALID 33563
11 rows selected

发现比以前多了两个表对象
这个就是该过程在执行后会产生两个表
一个是永久表MLOG$_EMP ，这个是一个TEST快照日志，记录TEST的在此之后，完成之前的DML语句
另一个就是临时表RUPD$_EMP

我们检查一下所有的表数据，已便与下面的结果对比
SQL> select count(*) from test;
101
SQL> select count(*) from MLOG$_test;
0
SQL> select count(*) from rupd$_test;
0
SQL> select count(*) from int_test;
101
可以看到，表的数据已经转移过来

SQL> select c from audit_test;
101
这里可以看到，触发器执行的还是原触发器

这里给大家介绍两个表的来源
SQL> select master,log_table from user_mview_logs;
TEST MLOG$_TEST

SQL> select mview_name,container_name, build_mode from user_mviews;
INT_TEST INT_TEST PREBUILT

6、在新表上创建新的约束与索引，这个步骤其实放在哪里都可以，但是一般的情况下，我们把主要的数据转移完之后，再建立索引速度可能会快一些。，为了便于测试，我们将在中间表上面创建一个与原表略有差异的触发器。

SQL> alter table int_test add constraint int_test_pk primary key(a);
Table altered
请注意该主键的名称，与原表的主键名称的差别。

create or replace trigger tr_int_test
before insert or update or delete on int_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update audit_test set c=c+2;
commit;
end;
Trigger created

可以看到，这个触发器的增加是增加2，原表的是增加1。
检查两个触发器，都是正常的。
SQL> select trigger_name, status from user_triggers;
TR_INT_TEST ENABLED
TR_TEST ENABLED

想想，这个时候，如果我们往原表插入数据，会有什么结果呢？是一个触发器工作，还是两个，还是别的可能？
SQL> insert into test values(102,1);
1 row inserted
SQL> commit;
Commit complete
再检查表的结果

SQL> select count(*) from test;
102
SQL> select count(*) from int_test;
101
SQL> select count(*) from mlog$_test;
1
SQL> select count(*) from rupd$_test;
0
SQL> select c from audit_test;
102
发现，其实原表增加了，中间表的记录并没有同步，只是快照日志增加了一行。触发器也只执行也原来的触发器

7、我们来执行表的同步，其实这一步不是必须，如果不执行这一步，在所有的重构完成之后（执行finish过程）也将自动执行以下过程。我们这里的目的，就是了解该过程怎么执行。
SQL> execute DBMS_REDEFINITION.SYNC_INTERIM_TABLE('MYTEST','TEST','INT_TEST');
PL/SQL procedure successfully completed

同步之后会有什么结果呢？我们查询如下的表

SQL> select count(*) from test;
102
SQL> select count(*) from int_test;
102
SQL> select count(*) from mlog$_test;
0

SQL> select count(*) from rupd$_test;
0
SQL> select c from audit_test;
102
可以看到，其实这个过程是把快照日志中的数据转移到了中间表，但是这个过程并不触发触发器。

8、我们需要完成最后的一些工作，如授权，检查TEST以前的权限，并在中间表上执行同样的权限，如果不做，那原表的权限将不会转移到新表，以下的测试将说明这个问题。
SQL> select * from USER_TAB_PRIVS_MADE;
SYSTEM TEST MYTEST SELECT NO NO
SYSTEM TEST MYTEST DELETE

为了以后的重构的表与以前一致，我们必须对中间表授原表同样的对象权限，这里，为了测试需要，我们保留一个原来的权限，还增加一个新的权限
SQL> grant select on int_test to system;
Grant succeeded
SQL> grant update on int_test to system;
Grant succeeded

完成之后的权限情况如下，可以与最终重构之后的权限情况对比。
SQL> select * from USER_TAB_PRIVS_MADE;
SYSTEM INT_TEST MYTEST SELECT NO NO
SYSTEM INT_TEST MYTEST UPDATE
SYSTEM TEST MYTEST SELECT NO NO
SYSTEM TEST MYTEST DELETE

9、所有的工作都准备完成，我们执行重构完成的过程，这个过程将执行表的交换。
SQL> execute DBMS_REDEFINITION.FINISH_REDEF_TABLE('MYTEST','TEST', 'INT_TEST');
PL/SQL procedure successfully completed

我们还是往test表中插入数据，将有什么结果呢？

SQL> insert into test values(103,1);
1 row inserted

SQL> select count(*) from test;
103

SQL> select count(*) from int_test;
102

SQL> select * from mlog$_test;
select * from mlog$_test
ORA-00942: table or view does not exist

SQL> select * from rupd$_test;
select * from rupd$_test
ORA-00942: table or view does not exist

SQL> select c from audit_test;
104

可以看到，这里的触发器执行的是以前在ini_test（中间表）上的触发器。 上一次是102，这次是104。

这里发生了一个很有趣的转换，其实整个核心就是这里，ORACLE这里完成了表test到int_test的 表换名的工作，只是所有的约束、索引或触发器名称还是保持了原来的名称

SQL> select t.index_name,t.table_name from user_indexes t;
INT_TEST_PK TEST
PK_TEST_ID INT_TEST

SQL> select t.trigger_name,t.table_name from user_triggers t;
TR_INT_TEST TEST
TR_TEST INT_TEST

我们检查对象权限
SQL> select * from USER_TAB_PRIVS_MADE; SYSTEM INT_TEST MYTEST SELECT NO NO
SYSTEM INT_TEST MYTEST DELETE
SYSTEM TEST MYTEST SELECT NO NO
SYSTEM TEST MYTEST UPDATE
这里可以看到，对于所有数据字典中的这两个名称，其实是完成了一个表的名字对换的过程，所以对于原表的特性，我们必须在新表中给予相同的特性，这样才能真正的转换到新的表。

10、大功告成，我们可以删除中间表，并且有可能的话，修改约束，索引，触发器的名称与原来一致，如果你认为不重要，可以不予修改。
SQL> drop table int_test;
Table dropped

如果是92以上版本，我们可以对索引，约束换名，但是以下版本，最好就是删除重新建立了
如果运行了START_REDEF_TABLE 过程开始，我们就必须运行
dbms_redefinition.abort_redef_table('MYTEST','TEST','INT_TEST');来终止整个重构过程，到这里，就算是完了。

SQL> select object_name, object_type, status, object_id, data_object_id from user_objects order by 4;

OBJECT_NAME OBJECT_TYPE STATUS OBJECT_ID DATA_OBJECT_ID
-------------------------------------------------------------------------------- ------------------ ------- --------- --------------
AUDIT_TEST TABLE VALID 33579 33579
TEST TABLE VALID 33582
TEST TABLE PARTITION VALID 33583 33589
TEST TABLE PARTITION VALID 33584 33590
TEST TABLE PARTITION VALID 33585 33591
TEST TABLE PARTITION VALID 33586 33592

6 rows selected
看到这里，你也应该相信转换已经完成了吧，现在是分区表了。

总结：
本文不想从大的框架上来说明问题，而是用一些例子来说明问题
这样可能会更让大家明白其中发生的一切。

这里尽量的模拟了实际可能会遇到的情况。
如约束，索引，触发器，对象权限，这个在重新定义的时候需要注意什么，需要我们手工完成什么

外键约束这里没有举例说明，其实与普通约束一样，只是在中间表上建立外键约束的时候，最好先禁止掉(DISABLE)，等完成之后再打开。

其实主要过程就是完成了一个表的记录的拷贝（通过快照），最后在数据字典中换名的工作。

欢迎大家补充，参考文档
metalink:
Doc ID: Note:177407.1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/64429/viewspace-917332/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/64429/viewspace-917332/