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
|
