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

王朝other·作者佚名  2008-05-19
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航