GoldenGate同步DML功能測試

GoldenGate同步DML功能測試：

GoldenGate官方文件中明確列出的不支援的資料型別（這些型別沒有做測試）：
ANYDATA ANYDATASET ANYTYPE BFILE BINARY_INTEGER MLSLABEL ORDDICOM PLS_INTEGER TIMEZONE_ABBR URITYPE UROWID

在本測試中，對包含各種資料型別（基本囊括除上述不支援的型別外的所有型別）的對錶做DML操作(包含insert、update、delete和merge），看源端的修改是否可以透過GoldenGate同步到目標資料庫中，同時也對分割槽表做了測試。

結果發現沒有什麼問題，同步正常，但會延遲10秒左右。

本文不涉及GoldenGate相關操作。

測試過程如下：

1、建立表：

將下列語句儲存為SQL指令碼，在源資料庫端和目標資料庫端分別執行：
-- create table
-- number type
create table test11(id number,name int);
create table test12(id number,name decimal);
create table test13(id number,name float);
create table test14(id number,name real);
create table test15(id number,name number);
create table test16(id number,name binary_float);
create table test17(id number,name binary_double);
-- character type
create table test21(id number,name char(10));
create table test22(id number,name varchar2(10));
create table test23(id number,name varchar(10));
create table test24(id number,name nchar(10));
create table test25(id number,name nvarchar2(10));
create table test26(id number,name long);
create table test27(id number,name rowid);
-- binary type
create table test31(id number,name raw(10));
create table test32(id number,name long raw);
-- time type
create table test41(id number,name date);
create table test42(id number,name timestamp);
create table test43(id number,name timestamp with time zone);
create table test44(id number,name timestamp with local time zone);
create table test45(id number,name interval year to month);
create table test46(id number,name interval day to second);
-- large object type
Create table test51(id number,name clob);
Create table test52(id number,name nclob);
Create table test53(id number,name blob);
-- varray type
Create type varray_name_type as varray(2) of varchar2(10);
/
Create table test54(id number,name varray_name_type);
-- nested table type
Create type name_type as object(first_name varchar2(10),last_name varchar2(10));
/
Create type nested_table_name_type as table of name_type;
/
Create table test55(id number,name nested_table_name_type) nested table name store as nested_name;
-- XML type
Create table test56(id number,name sys.xmltype);


2、插入資料：

將下列語句儲存為SQL指令碼，在源資料庫端執行，檢查目標端資料變化情況：
insert into test11 values(1,11111);
insert into test12 values(1,11111);
insert into test13 values(1,11111);
insert into test14 values(1,11111);
insert into test15 values(1,11111);
insert into test16 values(1,30.5f);
insert into test17 values(1,48.7d);

insert into test21 values(1,'a');
insert into test22 values(1,'a');
insert into test23 values(1,'a');
insert into test24 values(1,'a');
insert into test25 values(1,'a');
insert into test26 values(1,'AAAAAAAAABBBBBBBBB');
insert into test27 values(1,'AAAAAAAAABBBBBBBBB');

insert into test31 values(1,'1111100000');
insert into test32 values(1,'1111100000');

insert into test41 values(1,sysdate);
insert into test42 values(1,sysdate);
insert into test43 values(1,sysdate);
insert into test44 values(1,sysdate);
insert into test45 values(1,interval '30' year);
insert into test46 values(1,interval '30' day);

insert into test51 values(1,empty_clob());
update test51 set name='hello this is a test' where id=1;
insert into test52 values(1,empty_clob());
update test52 set name='how do you do' where id=1;
insert into test53 values(1,empty_blob());
update test53 set name='1111100000' where id=1;

insert into test54 values(1,varray_name_type('hello','hi'));
insert into test55 values(1,nested_table_name_type(name_type('Steve','White'),Name_type('John','Black')));
insert into test56 values(1,sys.xmltype.createXML('on'));
commit;

3、更新資料：

將下列語句儲存為SQL指令碼，在源資料庫端執行，檢查目標端資料變化情況：
update test11 set name=22222 where id=1;
update test12 set name=22222 where id=1;
update test13 set name=22222 where id=1;
update test14 set name=22222 where id=1;
update test15 set name=22222 where id=1;
update test16 set name=40.5f where id=1;
update test17 set name=58.7d where id=1;

update test21 set name='b' where id=1;
update test22 set name='b' where id=1;
update test23 set name='b' where id=1;
update test24 set name='b' where id=1;
update test25 set name='b' where id=1;
update test26 set name='BBBBBBBBBAAAAAAAAA' where id=1;
update test27 set name='BBBBBBBBBAAAAAAAAA' where id=1;

update test31 set name='0000011111' where id=1;
update test32 set name='0000011111' where id=1;

update test41 set name=sysdate-1 where id=1;
update test42 set name=sysdate-1 where id=1;
update test43 set name=sysdate-1 where id=1;
update test44 set name=sysdate-1 where id=1;
update test45 set name=interval '60' year where id=1;
update test46 set name=interval '60' day where id=1;

update test51 set name='this is a test' where id=1;
update test52 set name='do you do' where id=1;
update test53 set name='0000011111' where id=1;

update test54 set name=varray_name_type('hi','hello') where id=1;
update test55 set name=nested_table_name_type(name_type('White','Steve'),Name_type('Black','Jone')) where id=1;
update test56 set name=sys.xmltype.createXML('come') where id=1;
commit;

4、刪除資料：

將下列語句儲存為SQL指令碼，在源資料庫端執行，檢查目標端資料變化情況：
delete from test11 where id=1;
delete from test12 where id=1;
delete from test13 where id=1;
delete from test14 where id=1;
delete from test15 where id=1;
delete from test16 where id=1;
delete from test17 where id=1;

delete from test21 where id=1;
delete from test22 where id=1;
delete from test23 where id=1;
delete from test24 where id=1;
delete from test25 where id=1;
delete from test26 where id=1;
delete from test27 where id=1;

delete from test31 where id=1;
delete from test32 where id=1;

delete from test41 where id=1;
delete from test42 where id=1;
delete from test43 where id=1;
delete from test44 where id=1;
delete from test45 where id=1;
delete from test46 where id=1;

delete from test51 where id=1;
delete from test52 where id=1;
delete from test53 where id=1;
delete from test54 where id=1;
delete from test55 where id=1;
delete from test56 where id=1;
commit;

5、merge語句

1）在源資料庫端和目標資料庫端分別用下列語句建立test_from表和test_to表：
create table test_from(id number,name varchar2(10));
create table test_to(id number,name varchar2(10));
2）在源端資料庫執行下列操作：
insert into test_from values(1,'a');
insert into test_from values(2,'b');
insert into test_to values(2,'c');
commit;
3）在源資料庫端和目標資料庫端檢查test_from和test_to表資料情況：
select * from test_from;
select * from test_to;
4）在源資料庫端執行下列操作：
merge into test_to using test_from on (test_from.id=test_to.id) when matched then update set test_to.name=test_from.name when not matched then insert values(test_from.id,test_from.name);
commit;
5）在源資料庫端和目標資料庫端檢查test_from和test_to表資料情況：
select * from test_from;
select * from test_to;

6、分割槽表DML測試

1）在源資料庫端和目標資料庫端分別用下列語句建立test_partition_table表：
create table test_partition_table(id number,week_no number(2)) partition by range (week_no) (partition jan values less than(4),partition feb values less than(8),partition others values less than(maxvalue)) enable row movement;
2）在源資料庫端執行下列DML操作，每一次commit時都檢視目標端資料庫資料變化情況：
insert into test_partition_table values(1,2);
insert into test_partition_table values(2,6);
insert into test_partition_table values(3,10);
commit;
update test_partition_table set week_no=3 where id=1;
commit;
delete from test_partition_table where id=1;
commit;
3）測試row movement場景，在源資料庫端執行，檢視目標資料庫端資料變化情況：
update test_partition_table set week_no=9 where id=2;
commit;