[20170214]在线重定义测试.txt
--//以前测试过,重复测试,因为生产系统要做一次相同的操作.
--//实际的原理利用物化事务.注例子好像来源于piner的<构建oracle高可用环境>,当时版本是9i,好像没有
--//dbms_redefinition.copy_table_dependents函数.
1.准备工作:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table test(a int ,b int) tablespace users;
declare
i integer;
begin
for i in 1..100 loop
insert into test values(i,100-i);
end loop;
commit ;
end;
/
create table audit_test( c int) tablespace users;
insert into audit_test values(100);
commit ;
CREATE OR REPLACE TRIGGER tr_test
BEFORE INSERT OR UPDATE OR DELETE
ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE audit_test
SET c = c + 1;
COMMIT;
END;
/
--//使用触发器,因为生产系统也存在类似的定义.
ALTER TABLE TEST ADD CONSTRAINT pk_test_id PRIMARY KEY (a);
create index i_test_b on test(b);
2.检查触发器是否生效:
SCOTT@book> select * from audit_test;
C
----------
100
insert into test values(101,0);
commit
SCOTT@book> select * from audit_test;
C
----------
101
3.开始测试:
SCOTT@book> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'TEST');
PL/SQL procedure successfully completed.
--//说明可以在线重定义.
4.创建中间表：
CREATE table int_test (a int, b int,c 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)
);
--//增加一个字段c.并且采用分区表.
5.执行在线重新定义并且拷贝数据：
SCOTT@book> execute dbms_redefinition.start_redef_table('SCOTT','TEST','INT_TEST','a a,b b,0 c ');
PL/SQL procedure successfully completed.
--//检查发现数据已经更新过来了。
--//取消使用如下命令：
--//execute dbms_redefinition.abort_redef_table('SCOTT','TEST','INT_TEST');
SCOTT@book> select count(*) from int_test;
COUNT(*)
----------
101
--//说明数据已经进入中间表.
SCOTT@book> select master,log_table from user_mview_logs;
MASTER LOG_TABLE
------------------------------ ------------------------------
TEST MLOG$_TEST
SCOTT@book> SELECT mview_name, container_name, build_mode FROM user_mviews;
MVIEW_NAME CONTAINER_NAME BUILD_MOD
------------------------------ ------------------------------ ---------
INT_TEST INT_TEST PREBUILT
6.检查触发器并且增加数据看看：
SCOTT@book> select * from audit_test;
C
----------
101
SCOTT@book> insert into test values(102,2);
1 row created.
SCOTT@book> commit;
Commit complete.
SCOTT@book> select * from audit_test;
C
----------
102
SCOTT@book> select count(*) from int_test;
COUNT(*)
----------
101
SCOTT@book> select count(*) from test;
COUNT(*)
----------
102
--//可以发现audit_test表 还是加1(102).test记录数102，int_test记录数101.有一条没有同步过来。
7.在中间表上建立索引触发器等信息：
SCOTT@book> @ &r/desc_proc sys dbms_redefinition copy_table_dependents
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats
OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DATA_TYPE DEFAULTED
----- -------------------- --------------------- -------- ---------------- -------------- --------- -------------- ----------
SYS DBMS_REDEFINITION COPY_TABLE_DEPENDENTS 11 COPY_MVLOG PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
10 COPY_STATISTICS PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
9 NUM_ERRORS BINARY_INTEGER OUT BINARY_INTEGER N
8 IGNORE_ERRORS PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
7 COPY_PRIVILEGES PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
6 COPY_CONSTRAINTS PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
5 COPY_TRIGGERS PL/SQL BOOLEAN IN PL/SQL BOOLEAN Y
4 COPY_INDEXES BINARY_INTEGER IN BINARY_INTEGER Y
3 INT_TABLE VARCHAR2 IN VARCHAR2 N
2 ORIG_TABLE VARCHAR2 IN VARCHAR2 N
1 UNAME VARCHAR2 IN VARCHAR2 N
11 rows selected.
set serverout on size 1000000
declare
l_err_cnt integer :=0;
begin
dbms_redefinition.copy_table_dependents('SCOTT','TEST','INT_TEST',1,TRUE, TRUE, TRUE, FALSE, l_err_cnt);
dbms_output.put_line('Num Errors: ' || l_err_cnt);
end;
/
Num Errors: 0
PL/SQL procedure successfully completed.
--//检查发现主键，触发器都迁移过来了。
SCOTT@book> select index_name,index_type,TABLE_OWNER,TABLE_NAME,UNIQUENESS from user_indexes where table_name='INT_TEST';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES
------------------------------ --------------------------- ------------------------------ ---------- ---------
TMP$$_I_TEST_B0 NORMAL SCOTT INT_TEST NONUNIQUE
TMP$$_PK_TEST_ID0 NORMAL SCOTT INT_TEST UNIQUE
SCOTT@book> select * from USER_TRIGGERS;
TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNE BASE_OBJECT_TYPE TABLE_NAME COLUMN_NAM REFERENCING_NAMES WHEN_CLAUS STATUS DESCRIPTION ACTION_TYPE CROSSED BEF BEF AFT AFT INS FIR APP
-------------- --------------- -------------------------- ---------- ---------------- ---------- ---------- -------------------- ---------- -------- ------------------------------ ----------- ------- --- --- --- --- --- --- ---
TR_TEST BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT TABLE TEST REFERENCING NEW AS N ENABLED tr_test PL/SQL NO NO NO NO NO NO YES NO
EW OLD AS OLD BEFORE INSERT OR UPDATE OR
DELETE
ON TEST
FOR EACH ROW
TMP$$_TR_TEST0 BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT TABLE INT_TEST REFERENCING NEW AS N ENABLED "SCOTT"."TMP$$_TR_TEST0" PL/SQL NO NO NO NO NO NO YES NO
EW OLD AS OLD BEFORE UPDATE OR INSERT OR DEL
ETE ON "SCOTT"."INT_TEST" FOR
EACH ROW
8.同步操作:
--//如果迁移时间很长,可以在结束前做一次同步操作.
SCOTT@book> select count(*) from test;
COUNT(*)
----------
102
SCOTT@book> select count(*) from int_test;
COUNT(*)
----------
101
SCOTT@book> EXEC dbms_redefinition.sync_interim_table('SCOTT', 'TEST', 'INT_TEST');
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from int_test;
COUNT(*)
----------
102
SCOTT@book> select count(*) from test;
COUNT(*)
----------
102
9.继续插入test表:
SCOTT@book> insert into test values(103,3);
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select count(*) from test;
COUNT(*)
----------
103
SCOTT@book> select count(*) from int_test;
COUNT(*)
----------
102
SCOTT@book> select * from audit_test;
C
----------
103
--//可以发现audit_test 还是加1.
10.收尾工作:
SCOTT@book> exec dbms_redefinition.finish_redef_table('SCOTT','TEST','INT_TEST');
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from int_test;
COUNT(*)
----------
103
SCOTT@book> select count(*) from test;
COUNT(*)
----------
103
SCOTT@book> select SEGMENT_NAME,PARTITION_NAME from user_segments where segment_name='TEST';
SEGMENT_NAME PARTITION_NAME
-------------------- ------------------------------
TEST P10
TEST P20
TEST P30
SCOTT@book> select index_name,index_type,TABLE_OWNER,TABLE_NAME,UNIQUENESS from user_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME UNIQUENES
------------------------------ --------------------------- ---------- ---------- ---------
I_TEST_B NORMAL SCOTT TEST NONUNIQUE
PK_TEST_ID NORMAL SCOTT TEST UNIQUE
SCOTT@book> select * from USER_TRIGGERS;
TRIGGER_NAME TRIGGER_TYPE TRIGGERING_EVENT TABLE_OWNE BASE_OBJECT_TYPE TABLE_NAME COLUMN_NAM REFERENCING_NAMES WHEN_CLAUS STATUS DESCRIPTION ACTION_TYPE CROSSED BEF BEF AFT AFT INS FIR APP
-------------- --------------- -------------------------- ---------- ---------------- ---------- ---------- -------------------- ---------- -------- ------------------------------ ----------- ------- --- --- --- --- --- --- ---
TMP$$_TR_TEST0 BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT TABLE INT_TEST REFERENCING NEW AS N ENABLED "TMP$$_TR_TEST0" BEFORE INSERT PL/SQL NO NO NO NO NO NO YES NO
EW OLD AS OLD OR UPDATE OR DELETE
ON "INT_TEST"
FOR EACH ROW
TR_TEST BEFORE EACH ROW INSERT OR UPDATE OR DELETE SCOTT TABLE TEST REFERENCING NEW AS N ENABLED "SCOTT"."TR_TEST" BEFORE UPDAT PL/SQL NO NO NO NO NO NO YES NO
EW OLD AS OLD E OR INSERT OR DELETE ON "SCOT
T"."TEST" FOR EACH ROW
--//索引名字与触发器还是原来的名字.
SCOTT@book> insert into test values(104,4);
insert into test values(104,4)
*
ERROR at line 1:
ORA-00947: not enough values
--//字段已经多了1个.
SCOTT@book> select * from audit_test;
C
----------
103
SCOTT@book> insert into test values(104,4,1);
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select * from audit_test;
C
----------
104
--//OK一切正常,补充1点中间表的定义不是很好.应该安全一点定义最大值作为一个分区.
--//alter table scott.int_test add partition pmax values less than (maxvalue) ;
SCOTT@book> alter table scott.test add partition pmax values less than (maxvalue) ;
Table altered.
--//另外注意一个小问题,注意看~,实际上重定义后触发器的定义里面包括了schema,正常应用没有问题,如果你迁移到其他模式,触发器源
--//码会原样拷贝,这样存在一点点小问题.这也是在我一次改变schema遇到的问题.
SCOTT@book> select * from dba_source where owner='SCOTT' and name in ('TR_TEST','TMP$$_TR_TEST0');
OWNER NAME TYPE LINE TEXT
------ -------------------- ------------ ----------------------------------------------------------------------------------
SCOTT TMP$$_TR_TEST0 TRIGGER 1 TRIGGER
SCOTT TMP$$_TR_TEST0 TRIGGER 2 "TMP$$_TR_TEST0" BEFORE INSERT OR UPDATE OR DELETE
SCOTT TMP$$_TR_TEST0 TRIGGER 3 ON "INT_TEST"
SCOTT TMP$$_TR_TEST0 TRIGGER 4 FOR EACH ROW
SCOTT TMP$$_TR_TEST0 TRIGGER 5 DECLARE
SCOTT TMP$$_TR_TEST0 TRIGGER 6 PRAGMA AUTONOMOUS_TRANSACTION;
SCOTT TMP$$_TR_TEST0 TRIGGER 7 BEGIN
SCOTT TMP$$_TR_TEST0 TRIGGER 8 UPDATE audit_test
SCOTT TMP$$_TR_TEST0 TRIGGER 9 SET c = c + 1;
SCOTT TMP$$_TR_TEST0 TRIGGER 10 COMMIT;
SCOTT TMP$$_TR_TEST0 TRIGGER 11 END;
SCOTT TR_TEST TRIGGER 1 TRIGGER
SCOTT TR_TEST TRIGGER 2 "SCOTT"."TR_TEST" BEFORE UPDATE OR INSERT OR DELETE ON "SCOTT"."TEST" FOR EACH ROW
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SCOTT TR_TEST TRIGGER 3 DECLARE
SCOTT TR_TEST TRIGGER 4 PRAGMA AUTONOMOUS_TRANSACTION;
SCOTT TR_TEST TRIGGER 5 BEGIN
SCOTT TR_TEST TRIGGER 6 UPDATE audit_test
SCOTT TR_TEST TRIGGER 7 SET c = c + 1;
SCOTT TR_TEST TRIGGER 8 COMMIT;
SCOTT TR_TEST TRIGGER 9 END;
SCOTT TR_TEST TRIGGER 10
21 rows selected.
--// [20170203]克隆schema.txt 链接:http://blog.itpub.net/267265/viewspace-2133034/
$ impdp system/oracle directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
Import: Release 11.2.0.4.0 - Production on Tue Feb 14 09:34:16 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/a* directory=DATA_PUMP_DIR network_link=loopback REMAP_SCHEMA=scott:ztest REMAP_TABLESPACE=users:users SCHEMAS=scott
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 25.25 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "ZTEST"."TEST":"P10" 49 rows
. . imported "ZTEST"."TEST":"P20" 50 rows
. . imported "ZTEST"."TEST":"P30" 5 rows
. . imported "ZTEST"."SESSION_WAIT_RECORD" 8122 rows
. . imported "ZTEST"."LOCK_OBJECT_RECORD" 8122 rows
. . imported "ZTEST"."AUDIT_TEST" 1 rows
. . imported "ZTEST"."DEPT" 4 rows
. . imported "ZTEST"."EMP" 14 rows
. . imported "ZTEST"."INT_TEST" 103 rows
. . imported "ZTEST"."SALGRADE" 5 rows
. . imported "ZTEST"."BONUS" 0 rows
. . imported "ZTEST"."TEST":"P40" 0 rows
. . imported "ZTEST"."TEST":"PMAX" 0 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Feb 14 09:34:41 2017 elapsed 0 00:00:24
SCOTT@book> select * from dba_source where owner='ZTEST' and name in ('TR_TEST');
OWNER NAME TYPE LINE TEXT
------ ------- ------- ---- -------------------------------------------------------------------------------------------
ZTEST TR_TEST TRIGGER 1 TRIGGER "ZTEST"."TR_TEST" BEFORE UPDATE OR INSERT OR DELETE ON "SCOTT"."TEST" FOR EACH ROW
~~~~~~~~~~~~~~
ZTEST TR_TEST TRIGGER 2 DECLARE
ZTEST TR_TEST TRIGGER 3 PRAGMA AUTONOMOUS_TRANSACTION;
ZTEST TR_TEST TRIGGER 4 BEGIN
ZTEST TR_TEST TRIGGER 5 UPDATE audit_test
ZTEST TR_TEST TRIGGER 6 SET c = c + 1;
ZTEST TR_TEST TRIGGER 7 COMMIT;
ZTEST TR_TEST TRIGGER 8 END;
ZTEST TR_TEST TRIGGER 9
9 rows selected.
--//注意看~,这也是迁移中注意的问题,不单单是在线重定义.
--//这样在scott用户插入一行.
SCOTT@book> insert into test values(105,4,1);
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select * from audit_test;
C
----------
105
SCOTT@book> select * from ztest.audit_test;
C
----------
105
--//注意ztest.audit_test也增加1.
SCOTT@book> select count(*) from test ;
COUNT(*)
----------
105
SCOTT@book> select count(*) from ztest.test ;
COUNT(*)
----------
104