|
创建我们需要重新定义的中间表这个是一个分区表以后我们将把原表的所有数据在线转移到该表上来 SQL>create table int_test (a intb int) partition by range(a) (partition p values less than() partition p values less than() partition p values less than() partition p values less than()); Table created 然后我们检查所有对象所有对象共个 SQL> select object_nameobject_typestatusobject_iddata_object_id from user_objects; AUDIT_TEST TABLE VALID INT_TEST TABLE PARTITION VALID INT_TEST TABLE PARTITION VALID INT_TEST TABLE PARTITION VALID INT_TEST TABLE PARTITION VALID INT_TEST TABLE VALID PK_TEST_ID INDEX VALID TEST TABLE VALID TR_TEST TRIGGER VALID rows selected 执行在线重新定义开始 SQL> execute DBMS_REDEFINITIONSTART_REDEF_TABLE(MYTESTTESTINT_TEST); begin SYSDBMS_REDEFINITIONSTART_REDEF_TABLE(MYTESTTESTINT_TEST); end; ORA: insufficient privileges ORA: at SYSDBMS_REDEFINITION line ORA: at SYSDBMS_REDEFINITION line ORA: at line 这里存储过程返回一个错误说没有足够的权限的确是这样经查如果要执行这个存储过程起码需要如下权限 * CREATE ANY TABLE * ALTER ANY TABLE * DROP ANY TABLE * LOCK ANY TABLE * SELECT ANY TABLE 我们可以临时授予DBA权限给用户完之后取消掉或转移到其他有权限的用户执行这个操作 SQL> CONNECT SYSTEM/CHEN Connected to Oraclei Enterprise Edition Release Connected as SYSTEM SQL> execute SYSDBMS_REDEFINITIONSTART_REDEF_TABLE(MYTESTTESTINT_TEST); PL/SQL procedure successfully completed 这里可以看到我们用system执行成功那么这个过程到底两个表执行了那些语句呢？我们可以通过如下的语句来验证一下 SQL> select sql_text from v$sqlarea where sql_text like %TEST%; SQL_TEXT
SELECT CURRENT$ACURRENT$B FROM (SELECT TESTA ATESTB B FROM select * from MYTESTTEST ALTER TRIGGER MYTESTTR_TEST COMPILE DEBUG SELECT /*+ NO_MERGE NO_MERGE(LL$) ROWID(MAS$) ORDERED USE_NL(MAS$) NO_INDEX(MAS DELETE FROM MYTESTINT_TEST SNAP$ WHERE A = : truncate table MYTESTINT_TEST purge snapshot log delete from MYTESTMLOG$_TEST where snaptime$$ <= : INSERT INTO MYTESTINT_TEST (AB) VALUES (::) select sql_text from v$sqlarea where sql_text like %TEST% UPDATE MYTESTINT_TEST SET A = :B = : WHERE A = : SELECT TESTA ATESTB B FROM MYTESTTEST TEST begin dbms_redefinitioncan_redef_table(MYTEST TEST); end; select count(*) from snap$ where (vname sowner) in ((TESTMYTEST)) comment on table MYTESTRUPD$_TEST is temporary updatable snapshot log begin DBMS_REDEFINITIONSTART_REDEF_TABLE(MYTESTTESTINT_TEST); end; comment on table MYTESTMLOG$_TEST is snapshot log for master table MYTEST INSERT INTO MYTESTINT_TEST(AB) SELECT TESTATESTB FROM MYTE INSERT /*+ APPEND */ INTO MYTESTINT_TEST(AB) SELECT TESTATEST update MYTESTMLOG$_TEST set snaptime$$ = : where snaptime$$ > to_date( SELECT DISTINCT LOG$A FROM (SELECT MLOG$A FROM MYTESTMLOG$_TEST MLOG$ rows selected 以上的语句我不再解释整个内部过程我也不再解释只是需要大家明白其实这里的主要需要了解的是就是把原表的数据给中间表复制了一份 我们再检查所有对象 SQL> select object_name object_type status object_id data_object_id from user_objects order by ; TEST TABLE VALID AUDIT_TEST TABLE VALID TR_TEST TRIGGER VALID PK_TEST_ID INDEX VALID INT_TEST TABLE VALID INT_TEST TABLE PARTITION VALID INT_TEST TABLE PARTITION VALID INT_TEST TABLE PARTITION VALID INT_TEST TABLE PARTITION VALID MLOG$_TEST TABLE VALID RUPD$_TEST TABLE VALID rows selected 发现比以前多了两个表对象 这个就是该过程在执行后会产生两个表 一个是永久表MLOG$_EMP 这个是一个TEST快照日志记录TEST的在此之后完成之前的DML语句 另一个就是临时表RUPD$_EMP 我们检查一下所有的表数据已便与下面的结果对比 SQL> select count(*) from test; SQL> select count(*) from MLOG$_test; SQL> select count(*) from rupd$_test; SQL> select count(*) from int_test; 可以看到表的数据已经转移过来 SQL> select c from audit_test; 这里可以看到触发器执行的还是原触发器 这里给大家介绍两个表的来源 SQL> select masterlog_table from user_mview_logs; TEST MLOG$_TEST SQL> select mview_namecontainer_name build_mode from user_mviews; INT_TEST INT_TEST PREBUILT |
