Hi,
I have a requirement to audit a table when ever it undergo any changes through any DML operations ( all data I need to capture ).
currently I am trying with one demo table, so that if I could to do it then I have to plan for few more tables.
Initially I thought of doing through the ROW LEVEL trigger , but if it required for entire schema then it will be difficult and may have slowness.
So I am trying with the DBMS_CDC_PUBLISH data capture feature, but I could not see any data in target table.
can you please help me and guide if the below script has anything wrong, why I am not getting any data in target table
CREATE
TABLE cdc_demo_employees AS
SELECT * FROM emp;
ALTER TABLE cdc_demo_employees ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
EXEC dbms_capture_adm.prepare_table_instantiation(table_name =>
'CDC_DEMO_EMPLOYEES');
EXEC dbms_capture_adm.prepare_table_instantiation('CDC_DEMO_EMPLOYEES');
SELECT
set_name,
change_source_name,
capture_name,
queue_name,
publisher,
stop_on_ddl
FROM
all_change_sets;
EXEC dbms_cdc_publish.create_change_set('CDC_DEMO_SET_TEST', 'CDC demo',
'HOTLOG_SOURCE', 'Y', NULL, NULL);
SELECT
*
FROM
USER_TABLESPACES;
EXEC SYS.dbms_cdc_publish.create_change_table('P1APP', 'EMP_CHANGE1',
'CDC_DEMO_SET_TEST', 'P1APP', 'EMP',
'EMPNO NUMBER(4), ENAME VARCHAR2(50), JOB VARCHAR2(10), MGR NUMBER(4), HIRE_DATE DATE,SAL NUMBER(7,2), COMM NUMBER(4), DEPTNO NUMBER(2) '
, 'BOTH', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', 'TABLESPACE R6DEV_D');
BEGIN
dbms_cdc_publish.create_change_table('R6DEV', 'CDC_DEMO_EMP_CDD',
'CDC_DEMO_SET_TEST', 'R6DEV', 'CDC_DEMO_EMPLOYEES',
'EMPNO NUMBER(4), ENAME VARCHAR2(50), JOB VARCHAR2(10), MGR NUMBER(4), HIRE_DATE DATE,SAL NUMBER(7,2), COMM NUMBER(4), DEPTNO NUMBER(2) '
, 'BOTH', 'Y', 'N', 'N', 'N', 'N', 'N', 'Y', NULL);
END;
EXEC dbms_cdc_publish.alter_change_set('CDC_DEMO_SET_TEST', enable_capture =>
'Y');
BEGIN
dbms_cdc_subscribe.create_subscription('CDC_DEMO_SET_TEST',
'Subx to cdc_demo_employees', 'CDC_DEMO_EMP_SUB1');
END;
/
BEGIN
dbms_cdc_subscribe.subscribe('CDC_DEMO_EMP_SUB1', 'r6dev',
'CDC_DEMO_EMPLOYEES', 'EMPNO, ENAME, JOB, MGR, HIRE_DATE,SAL, COMM, DEPTNO' ,
'CDC_DEMO_SET_TEST_SUB');
END;
/
SELECT
*--change_set_name, source_schema_name, source_table_name
FROM
SYS.cdc_change_tables$;
INSERT
INTO
cdc_demo_employees
(
empno,
ename
)
VALUES
(
2133,
'test'
);
-- target table
SELECT
*
FROM
CDC_DEMO_EMP_CDD;