Tuesday, March 20, 2007

Apply DDL in a replcation environment - not working as expected in 8i

1. The structure of table t3 before applying the DDL

repadmin@TESTDBA> desc myowner.t3

Name Null? Type
---- ----- -------
A NOT NULL NUMBER
B NOT NULL NUMBER


2. Apply DDL through dbms_repcat.execute_ddl

repadmin@TESTDBA> @ddl.sql
PL/SQL procedure successfully completed.

repadmin@TESTDBA> ho more ddl.sql
begin
dbms_repcat.execute_ddl (
gname => 'RG_MYOWNER',
ddl_text => 'alter table myowner.t3 add (c date) '
);
end;
/

Note: before column b, c are added through the above API, there is only
column A in the table t3, which is a replicated table.

3. The structure of table t3 after applying the DDL
repadmin@TESTDBA> desc myowner.t3;

Name Null? Type
---- ----- ----

A NOT NULL NUMBER
B NOT NULL NUMBER
C DATE

4.Adding columns DDL in TESTDBA are replicated in TESTDBB as expected

repadmin@TESTDBA> desc myowner.t3@genqb
Name Null? Type
--- ---- -----

A NOT NULL NUMBER
B NOT NULL NUMBER
C DATE

5. DML is only good for the colum A and doesn't work for column B and C

repadmin@TESTDBA> insert into myowner.t3 values(1,2, sysdate);

1 row created.

repadmin@TESTDBA> commit;

Commit complete.

repadmin@TESTDBA> select * from myowner.t3;

A B C
---------- ---------- --------------------
1 2 20-MAR-2007 13:58:04

repadmin@TESTDBA> select * from myowner.t3@testdbb

A B C
---------- ---------- --------------------
1 0


repadmin@TESTDBA> insert into myowner.t3 values(2,3, sysdate);

1 row created.

repadmin@TESTDBA> commit;

Commit complete.

repadmin@TESTDBA> select * from myowner.t3
2 /

A B C
---------- ---------- --------------------
1 2 20-MAR-2007 13:58:04
2 3 20-MAR-2007 14:06:29

repadmin@TESTDBA> select * from myowner.t3@testdbb
2 /

A B C
--------- ---------- --------------------
1 0
2 0

No comments: