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
