ORACLE的在线表格重定义

在Oracle9i出现之前，你只能通过MOVE或导出和导入的方式来进行表格重定义，因此表格重定义的过程可能相当漫长或者说是一个离线过程，在此期间应用程序对该表的操作将失败，特别是大型的分区表上的操作（上次coolyl）就问到了这个问题，我们当时是建议他用move partition的办法解决，但是move可能很耗时并可能引起全局索引的失效（不过9i已经有了update global indexes）。除了这个，如果用exp，我们也不能保证exp的时候该表的数据没有改变（除非单用户），而imp更是一个漫长的过程。 
  
为了解决这个问题，Oracle9i在其DBMS_REDEFINITION软件包中引入了在线表格重定义功能。这个特性对24/7Oracle数据库来说非常重要，这是由于DBA现在可以在保持表格的可更新性的同时重新组织表格的结构。 
  
然而，在线表格重定义也不是完美无缺的。下面列出了Oracle9i重定义过程的部分限制。 
·你必须有足以维护两份表格拷贝的空间。  
·你不能更改主键栏。  
·表格必须有主键。  
·必须在同一个大纲中进行表格重定义。  
·在重定义操作完成之前，你不能对新加栏加以NOT NULL约束。  
·表格不能包含LONG、BFILE以及用户类型（UDT）。  
·不能重定义链表（clusteredtables）。  
·不能在SYS和SYSTEM大纲中重定义表格。  
·不能用具体化视图曰志（materializedview logs）来重定义表格；不能重定义含有具体化视图的表格。  
·不能在重定义过程中进行横向分集（horizontalsubsetting）。
基本步骤如下 
  
第一步：利用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 bymytest; 
User created 
SQL> grant connect,resource to mytest; 
Grant succeeded 
  
SQL> connect mytest/mytest; 
Connected to Oracle9i Enterprise EditionRelease 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 deleteon test 
   for each row 
declare 
   PRAGMA AUTONOMOUS_TRANSACTION; 
   begin 
     update audit_test setc=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'); 
  
begindbms_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 constraintpk_test_id primary key(a); 
Table altered 
  
SQL> execdbms_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 lessthan(100),       
  partition p30 values lessthan(150),       
  partition p40 values lessthan(200)); 
Table created 
  
然后我们检查所有对象，所有对象共9个 
  
SQL> selectobject_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 ANYTABLE   
      * DROP ANYTABLE   
      * LOCK ANYTABLE   
      * SELECT ANYTABLE   
   
我们可以临时授予DBA权限给用户，完之后取消掉，或转移到其他有权限的用户执行这个操作： 
  
SQL> CONNECT SYSTEM/CHEN 
Connected to Oracle9i Enterprise EditionRelease 9.2.0.4.0  
Connected as SYSTEM 
SQL> executeSYS.DBMS_REDEFINITION.START_REDEF_TABLE('MYTEST','TEST','INT_TEST'); 
PL/SQL procedure successfully completed 
  
这里可以看到，我们用system执行成功，那么这个过程到底两个表执行了那些语句呢？我们可以通过如下的语句来验证一下 
  
SQL> select sql_text from v$sqlarea wheresql_text like '%TEST%'; 
  
SQL_TEXT 
-------------------------------------------------------------------------------- 
SELECTCURRENT$."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_textlike '%TEST%' 
UPDATE "MYTEST"."INT_TEST"SET "A" = :1,"B" = :2 WHERE "A" = :1 
SELECT "TEST"."A""A","TEST"."B" "B" FROM"MYTEST"."TEST" "TEST" 
begindbms_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 tableMYTEST. 
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  wheresnaptime$$ > 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 fromuser_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 constraintint_test_pk primary key(a); 
Table altered 
请注意该主键的名称，与原表的主键名称的差别。 
  
create or replace trigger tr_int_test 
  before insert or update or deleteon int_test 
   for each row 
declare 
   PRAGMA AUTONOMOUS_TRANSACTION; 
   begin 
     update audit_test setc=c+2; 
     commit; 
   end; 
Trigger created 
  
可以看到，这个触发器的增加是增加2，原表的是增加1。 
检查两个触发器，都是正常的。 
SQL> select trigger_name, status fromuser_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(*) fromrupd$_test; 
          0 
SQL> select c from audit_test; 
                     102 
发现，其实原表增加了，中间表的记录并没有同步，只是快照曰志增加了一行。触发器也只执行也原来的触发器 
  
7、我们来执行表的同步，其实这一步不是必须，如果不执行这一步，在所有的重构完成之后（执行finish过程）也将自动执行以下过程。我们这里的目的，就是了解该过程怎么执行。 
SQL> executeDBMS_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(*) fromrupd$_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> executeDBMS_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 fromuser_indexes t; 
INT_TEST_PK                   TEST 
PK_TEST_ID                    INT_TEST 
  
SQL> select t.trigger_name,t.table_namefrom 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)，等完成之后再打开。 
  
其实主要过程就是完成了一个表的记录的拷贝（通过快照），最后在数据字典中换名的工作。

 

另法：

使用mview做切换, 或者使用onlineredefinition(其实就是mview的方法做了一下包装,降低了复杂度).
1. create new_table 
create table new_table (
   col list.....
)
tablespace xxx 
partition by xxx(
  partition list
)

create index ..../
alter table add constraints ...
create trigger ....
2. 创建mview.
create materialized view log on old_table(pk);
create materialized view on prebuilt table as
select * from old_table;

3. 
dbms_mview.refresh('new_table');
dbms_mview.refresh('new_table');
drop materialized view new_table;
rename old_table to old_table_bak;
rename new_table to old_table;