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

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