oracle cdc hotlog

docker run --privileged --name oracle11g -p 1521:1521 -v /home/oracle/install:/install jaspeen/oracle-11g

docker exec -it oracle11g bash
su oracle

sqlplus / as sysdba

alter system set streams_pool_size='10' java_pool_size=50000000;

create tablespace ts_cdcpub datafile 'ts_cdcpub01.dbf' size 300m;


CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
QUOTA UNLIMITED ON SYSTEM
QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO cdcpub;
GRANT CREATE TABLE TO cdcpub;
GRANT CREATE TABLESPACE TO cdcpub;
GRANT UNLIMITED TABLESPACE TO cdcpub;
GRANT SELECT_CATALOG_ROLE TO cdcpub;
GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
GRANT CREATE SEQUENCE TO cdcpub;
GRANT CONNECT, RESOURCE, DBA TO cdcpub;


CREATE USER pink IDENTIFIED BY pink
QUOTA UNLIMITED ON SYSTEM 
QUOTA UNLIMITED ON SYSAUX;
GRANT CREATE SESSION TO pink;
GRANT CREATE TABLE TO pink;
GRANT CREATE TABLESPACE TO pink;
GRANT UNLIMITED TABLESPACE TO pink;
GRANT SELECT_CATALOG_ROLE TO pink;
GRANT EXECUTE_CATALOG_ROLE TO pink;
GRANT CREATE SEQUENCE TO pink;
GRANT CONNECT, RESOURCE, DBA TO pink;


EXECUTE DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE => 'cdcpub')



ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

conn pink/pink

CREATE TABLE kiki
(
ID NUMBER(12),
NAME VARCHAR2(23)
);

conn / as sysdba

BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(TABLE_NAME => 'pink.kiki');
END;
/


BEGIN
       DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(
       change_set_name => 'PINK_TEST',
       description => 'Testing',
       change_source_name => 'HOTLOG_SOURCE',
       stop_on_ddl => 'y',
       begin_date => sysdate,
       end_date => sysdate+100);
END;
/


BEGIN
       DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
       owner => 'cdcpub',
       change_table_name => 'test_ct_pink',
       change_set_name => 'PINK_TEST',
       source_schema => 'PINK',
       source_table => 'KIKI',
       column_type_list   => 'ID NUMBER(12), NAME VARCHAR2(23)',
       capture_values => 'both',
       rs_id => 'y',
       row_id => 'n',
       user_id => 'n',
       timestamp => 'n',
       object_id => 'n',
       source_colmap => 'n',
       target_colmap => 'y',
       options_string => 'TABLESPACE TS_CDCPUB');
END;
/


BEGIN
   DBMS_CDC_PUBLISH.ALTER_CHANGE_SET(
      change_set_name => 'PINK_TEST',
      enable_capture => 'y');
END;
/


GRANT SELECT ON cdcpub.test_ct_pink TO pink;

conn cdcpub/cdcpub

SELECT UNIQUE CHANGE_SET_NAME, COLUMN_NAME, PUB_ID
FROM ALL_PUBLISHED_COLUMNS
WHERE SOURCE_SCHEMA_NAME ='PINK' AND SOURCE_TABLE_NAME = 'KIKI';

BEGIN
       DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
       change_set_name => 'PINK_TEST',
       description => 'Change data for test',
       subscription_name => 'SUBS_PINK');
END;
/

BEGIN
       DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
       subscription_name => 'SUBS_PINK',
       source_schema => 'PINK',
       source_table => 'KIKI',
       column_list => 'ID,NAME',
       subscriber_view => 'SUBS_PINK_VIEW');
END;
/

BEGIN
   DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
       subscription_name => 'SUBS_PINK');
END;
/

BEGIN
   DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
       subscription_name => 'SUBS_PINK');
END;
/

conn pink/pink

insert into pink.kiki values('1','AMITRATHssss');

conn cdcpub/cdcpub

BEGIN
   DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(
       subscription_name => 'SUBS_PINK');
END;
/

SELECT * FROM SUBS_PINK_VIEW;