Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction commits.
Once use this feature for change notification of column level, and found that once the amount of updated records is more than a small value during a transaction, such as 200, then the change notification considers the corresponding tables are fully updated and it consumes much database resources, such as CPU time, I/O throughput, etc, so just as Oracle official document said, For best performance of change notification, Registered objects are few and mostly read-only and that modifications to those objects are the exception rather than the rule. If the object is extremely volatile, then it cause a large number of invalidation notifications to be sent, and potentially a lot of storage in the invalidation queue on the server. If there are frequent and a large number of notifications, it can slow down OLTP throughput due to the overhead of generating the notifications.
The following is the example of how to use database change notification:
1.configuration job_queue_processes and create test user
SQL> alter system set job_queue_processes=1;
System altered.
SQL> create user test identified by test default tablespace users temporary tablespace temp quota unlimited on users;
User created.
SQL> grant connect,create table,create procedure,create sequence to test;
Grant succeeded.
SQL> grant change notification to test;
Grant succeeded.
SQL> grant execute on dbms_change_notification to test;
Grant succeeded.
2.Create test table
SQL> conn test/test
Connected.
SQL> create table t1 (id number,description varchar2(50),constraint t1_pk primary key (id));
Table created.
SQL> insert into t1 values (1,'One');
1 row created.
SQL> insert into t1 values (2,'Two');
1 row created.
SQL> commit;
Commit complete.
SQL> create table t2 ( id number,t1_id number,description varchar2(50),constraint t2_pk primary key (id),
2 constraint t2_t1_fk foreign key (t1_id) references t1 (id));
Table created.
SQL> insert into t2 values (1,1,'One-One');
1 row created.
SQL> insert into t2 values (2,1,'Two-One');
1 row created.
SQL> insert into t2 values (3,2,'Three-Two');
1 row created.
SQL> insert into t2 values (4,2,'Four-Two');
1 row created.
SQL> commit;
Commit complete.
3.Create record table
SQL> create table notifications (id number,message varchar2(4000),notification_date date);
Table created.
SQL> create sequence notifications_seq;
Sequence created.
4.Create notice processing program
CREATE OR REPLACE PROCEDURE tables_changed_chnt(ntfnds IN SYS.chnf$_desc) IS
l_regid NUMBER;
l_table_name VARCHAR2(60);
l_event_type NUMBER;
l_numtables NUMBER;
l_operation_type NUMBER;
l_numrows NUMBER;
l_row_id VARCHAR2(20);
l_operation VARCHAR2(20);
l_message VARCHAR2(4000) := NULL;
BEGIN
l_regid := ntfnds.registration_id;
l_numtables := ntfnds.numtables;
l_event_type := ntfnds.event_type;
IF l_event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE THEN
FOR i IN 1 .. l_numtables LOOP
l_table_name := ntfnds.table_desc_array(i).table_name;
l_operation_type := ntfnds.table_desc_array(i).Opflags;
l_row_id:= ntfnds.table_desc_array(i).row_desc_array(1).row_id;
IF (BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
l_numrows := ntfnds.table_desc_array(i).numrows;
ELSE
l_numrows := 0; /* ROWID INFO NOT AVAILABLE */
END IF;
CASE
WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.INSERTOP) != 0 THEN
l_operation := 'Records Inserted';
WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.UPDATEOP) != 0 THEN
l_operation := 'Records Updated';
WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.DELETEOP) != 0 THEN
l_operation := 'Records Deleted';
WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.ALTEROP) != 0 THEN
l_operation := 'Table Altered';
WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.DROPOP) != 0 THEN
l_operation := 'Table Dropped';
WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.UNKNOWNOP) != 0 THEN
l_operation := 'Unknown Operation';
ELSE
l_operation := '?';
END CASE;
l_message := 'Table (' || l_table_name || ') - ' || l_operation || '. Rows=' || l_numrows||' rowid='||l_row_id;
INSERT INTO notifications (id, message, notification_date)
VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
COMMIT;
END LOOP;
END IF;
END;
/
DECLARE
l_regds SYS.CHNF$_REG_INFO;
l_regid NUMBER;
l_qosflags NUMBER;
l_id t1.id%TYPE;
BEGIN
l_qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE +
DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
l_regds := SYS.CHNF$_REG_INFO ('tables_changed_chnt', l_qosflags, 0,0,0);
l_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start (l_regds);
SELECT id into l_id FROM t1 WHERE ROWNUM = 1;
SELECT id into l_id FROM t2 WHERE ROWNUM = 1;
DBMS_CHANGE_NOTIFICATION.reg_end;
END;
/
PL/SQL procedure successfully completed.
5.Test
SQL> insert into t1 values (4,'Four');
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select * from notifications;
ID MESSAGE NOTIFICATION_DATE
---------- ---------------------------------------------------------------------- -------------------
1 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
2 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
SQL> select * from t2;
ID T1_ID DESCRIPTION
---------- ---------- --------------------------------------------------
1 1 One-One
2 1 Two-One
3 2 Three-Two
4 2 Four-Two
SQL> insert into t2 values (5,4,'Five-f');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from notifications;
ID MESSAGE NOTIFICATION_DATE
---------- ---------------------------------------------------------------------- -------------------
1 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
2 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
3 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
4 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
SQL> update t2 set description='Five-Four' where id=5;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from notifications;
ID MESSAGE NOTIFICATION_DATE
---------- ---------------------------------------------------------------------- -------------------
1 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
2 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
3 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
4 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
5 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:31:54
6 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:31:54
6 rows selected.
SQL> delete t2 where id=5;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from notifications;
ID MESSAGE NOTIFICATION_DATE
---------- ---------------------------------------------------------------------- -------------------
1 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
2 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
3 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
4 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
5 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:31:54
6 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:31:54
6 rows selected.
---------- Don't konw why there is no record delete opration.
SQL> delete t1 where id=4;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from notifications;
ID MESSAGE NOTIFICATION_DATE
---------- ---------------------------------------------------------------------- -------------------
1 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
2 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:27:49
3 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
4 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:30:59
5 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:31:54
6 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:31:54
7 Table (TEST.T2) - Records Deleted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:32:29
8 Table (TEST.T2) - Records Deleted. Rows=1 rowid=AAANBhAAEAAAAGUAAE 2011-09-20 11:32:29
9 Table (TEST.T1) - Records Deleted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:33:40
10 Table (TEST.T1) - Records Deleted. Rows=1 rowid=AAANBfAAEAAAAGEAAD 2011-09-20 11:33:40
10 rows selected.
-------- Why here have the delete opration.