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 33567MLOG$_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$_TESTSQL> select mview_name,container_name, build_mode from user_mviews;
INT_TEST INT_TEST PREBUILT
