* A somewhat chaotic test of CDC. CDC is eazy to understand. But to demonstrate it * you need to have 2 SQL*PLUS windows and go back and forth. SQL> SQL> -- Source Views : user_piblications? DBA_subscriptions, user_published_columns SQL> -- subscriber views : all_published_columns, USER_Subscriptions, SQL> -- USER_subscribed_columns, USER_Subscribed_tables SQL> SQL> conn sys/athena@athena as sysdba Connected. SQL> grant execute_catalog_role to jyang; Grant succeeded. SQL> grant select_catalog_role to jyang; Grant succeeded. SQL> grant execute on DBMS_LOGMNR_CDC_SUBSCRIBE to scott; Grant succeeded. SQL> conn jyang/jyang@athena Connected. SQL> select * from cdc_test; C1 C2 -- ---------- a 1 b 2 c 3 a 4 a 5 a 6 a 7 a 6 a 7 e 9 w 0 C1 C2 -- ---------- P 0 G 3 X 2 14 rows selected. SQL> grant select on cdc_test to scott; Grant succeeded. SQL> SQL> conn scott/tiger@athena Connected. SQL> -- Remove all subscriptions and subscriber views bwfore dropping teh change table. SQL> select * from user_subscriptions; HANDLE SET_NAME USERNAME ---------- ------------------------------ ------------------------------ CREATED S EARLIEST_SCN LATEST_SCN DESCRIPTION LAST_PURG --------- - ------------ ---------- ------------------------------ --------- LAST_EXTE --------- 41 SYNC_SET SCOTT 01-JUL-03 A 91030114 91095229 Change data for cdc_test 01-JUL-03 1 row selected. SQL> exec DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW(41,'JYANG','CDC_TEST'); PL/SQL procedure successfully completed. SQL> exec DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIPTION(41); PL/SQL procedure successfully completed. SQL> conn jyang/jyang@athena Connected. SQL> exec DBMS_CDC_PUBLISH.DROP_CHANGE_TABLE('jyang','cdc_test_ct','N'); PL/SQL procedure successfully completed. SQL> SQL> begin 2 DBMS_LOGMNR_CDC_PUBLISH.CREATE_CHANGE_TABLE (OWNER => 'jyang', 3 CHANGE_TABLE_NAME => 'cdc_test_ct', 4 CHANGE_SET_NAME => 'SYNC_SET', 5 SOURCE_SCHEMA => 'jyang', 6 SOURCE_TABLE => 'cdc_test', 7 COLUMN_TYPE_LIST => 'c1 varchar2(2), c2 number(1)', 8 CAPTURE_VALUES => 'both', 9 RS_ID => 'y', 10 ROW_ID => 'n', 11 USER_ID => 'n', 12 TIMESTAMP => 'n', 13 OBJECT_ID => 'n', 14 SOURCE_COLMAP => 'y', 15 TARGET_COLMAP => 'y', 16 OPTIONS_STRING => null); 17 end; 18 / PL/SQL procedure successfully completed. SQL> -- Grant the Change table, not source table SQL> grant select on cdc_test_ct to scott; Grant succeeded. SQL> desc cdc_test_ct Name Null? Type ----------------------------------------- -------- ---------------------------- OPERATION$ CHAR(2) CSCN$ NUMBER COMMIT_TIMESTAMP$ DATE RSID$ NUMBER SOURCE_COLMAP$ RAW(128) TARGET_COLMAP$ RAW(128) C1 VARCHAR2(2) C2 NUMBER(1) SQL> conn scott/tiger@athena Connected. SQL> SQL> variable subhandle number; SQL> begin 2 -- Why does SYS.DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE not work? 3 DBMS_LOGMNR_CDC_SUBSCRIBE.GET_SUBSCRIPTION_HANDLE ( 4 CHANGE_SET => 'SYNC_SET', 5 DESCRIPTION => 'Change data for cdc_test', 6 SUBSCRIPTION_HANDLE => :subhandle); 7 end; 8 / PL/SQL procedure successfully completed. SQL> print subhandle; SUBHANDLE ---------- 42 SQL> select * from user_subscriptions; HANDLE SET_NAME USERNAME ---------- ------------------------------ ------------------------------ CREATED S EARLIEST_SCN LATEST_SCN DESCRIPTION LAST_PURG --------- - ------------ ---------- ------------------------------ --------- LAST_EXTE --------- 42 SYNC_SET SCOTT 01-JUL-03 N 1 0 Change data for cdc_test 1 row selected. SQL> select * from all_published_columns; CHANGE_SET_NAME SOURCE_SCHEMA_NAME ------------------------------ ------------------------------ SOURCE_TABLE_NAME PUB_ID COLUMN_NAME ------------------------------ ---------- ------------------------------ DATA_TYPE -------------------------------------------------------------------------------- DATA_LENGTH DATA_PRECISION DATA_SCALE N ----------- -------------- ---------- - SYNC_SET JYANG CDC_TEST 54681 C1 VARCHAR2 2 Y CHANGE_SET_NAME SOURCE_SCHEMA_NAME ------------------------------ ------------------------------ SOURCE_TABLE_NAME PUB_ID COLUMN_NAME ------------------------------ ---------- ------------------------------ DATA_TYPE -------------------------------------------------------------------------------- DATA_LENGTH DATA_PRECISION DATA_SCALE N ----------- -------------- ---------- - SYNC_SET JYANG CDC_TEST 54681 C2 NUMBER 22 1 0 Y 2 rows selected. SQL> -- Use subhandle from above step for SUBSCRIPTION_HANDLE SQL> -- Source table, not change table, case-insensitive SQL> begin 2 DBMS_LOGMNR_CDC_SUBSCRIBE.SUBSCRIBE ( 3 SUBSCRIPTION_HANDLE => 42, 4 SOURCE_SCHEMA => 'jyang', 5 SOURCE_TABLE => 'cdc_test', 6 COLUMN_LIST => 'c1,c2'); 7 end; 8 / PL/SQL procedure successfully completed. SQL> exec DBMS_LOGMNR_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION (SUBSCRIPTION_HANDLE =>42); PL/SQL procedure successfully completed. SQL> variable viewname varchar2(30); SQL> begin 2 DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW ( 3 SUBSCRIPTION_HANDLE => 42, 4 SOURCE_SCHEMA => 'jyang', 5 SOURCE_TABLE => 'cdc_test', 6 VIEW_NAME => :viewname); 7 end; 8 / PL/SQL procedure successfully completed. SQL> print viewname; VIEWNAME -------------------------------- CDC#CV$4254681 SQL> select * from CDC#CV$4254681; no rows selected SQL> -- Insert another row into cdc_test from jyang <<<<<<---- 1st insert SQL> select * from CDC#CV$4254681; no rows selected -- Need to execute EXTEND_WINDOW first -- Can not execute EXTEND_WINDOW without dropping the view first. SQL> begin 2 DBMS_LOGMNR_CDC_SUBSCRIBE.DROP_SUBSCRIBER_VIEW ( 3 SUBSCRIPTION_HANDLE => 42, 4 SOURCE_SCHEMA => 'jyang', 5 SOURCE_TABLE => 'cdc_test'); 6 end; 7 / PL/SQL procedure successfully completed. SQL> exec DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW(SUBSCRIPTION_HANDLE => 21); BEGIN DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW(SUBSCRIPTION_HANDLE => 21); END; * ERROR at line 1: ORA-31425: subscription handle does not exist ORA-06512: at "SYS.DBMS_CDC_SUBSCRIBE", line 0 ORA-06512: at line 1 SQL> exec DBMS_LOGMNR_CDC_SUBSCRIBE.EXTEND_WINDOW(SUBSCRIPTION_HANDLE => 42); PL/SQL procedure successfully completed. SQL> variable viewname varchar2(30); SQL> begin 2 DBMS_LOGMNR_CDC_SUBSCRIBE.PREPARE_SUBSCRIBER_VIEW ( 3 SUBSCRIPTION_HANDLE => 42, 4 SOURCE_SCHEMA => 'jyang', 5 SOURCE_TABLE => 'cdc_test', 6 VIEW_NAME => :viewname); 7 end; 8 / PL/SQL procedure successfully completed. SQL> print viewname; VIEWNAME -------------------------------- CDC#CV$4254681 SQL> select * from CDC#CV$4254681; OP CSCN$ COMMIT_TI -- ---------- --------- TARGET_COLMAP$ -------------------------------------------------------------------------------- SOURCE_COLMAP$ -------------------------------------------------------------------------------- RSID$ C1 C2 ---------- -- ---------- I 91097837 01-JUL-03 FE FE 50003 v 0 1 row selected. SQL> -- Insert another row into cdc_test from jyang <<<<<<---- 2nd insert SQL> select * from CDC#CV$4254681; OP CSCN$ COMMIT_TI -- ---------- --------- TARGET_COLMAP$ -------------------------------------------------------------------------------- SOURCE_COLMAP$ -------------------------------------------------------------------------------- RSID$ C1 C2 ---------- -- ---------- I 91097837 01-JUL-03 FE FE 50003 v 0 1 row selected. SQL> -- To see more changed data, view must be dropped and window must be extended. SQL> -- If you don't first purge the window, you will see data from both inserts SQL> spool off