Database Change Notification

    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. create a demo table and inset data into the table

create table RTM_CHANGE_NOTIFICATION

(

  COL1 VARCHAR2(100),

  COL2 VARCHAR2(100),

  OID  NUMBER(20) not null

);

 

declare

begin

 

for rec in 1..100 loop

 

insert into rtm_change_notification (col1,col2,oid) values('XIECH2'||rec,'FLOYD'||rec,rec);

 

end loop;

 

commit;

end;

 

2. prepare plsql callback procedure

Create table nfcount(cnt number);

Insert into nfcount values(0);

Commit;

 

CREATE OR REPLACE PROCEDURE proc_chnf_callback (ntfnds IN SYS.CHNF$_DESC)

IS

BEGIN

update nfcount set cnt = cnt+1;

commit;

END;

/

 

3. Register query

-- procession for registration and deregister

------------------------------------------------------------------------------

-- registration

declare

  lv_reg_info sys.chnf$_reg_info;

  lv_reg_id number;

  lv_qosflags number;

  lv_temp varchar2(3000);

  lv_operations_filter number;

begin

  lv_qosflags := dbms_change_notification.QOS_ROWIDS;

  lv_operations_filter := dbms_change_notification.INSERTOP+dbms_change_notification.UPDATEOP+dbms_change_notification.DELETEOP ;

  lv_reg_info := sys.chnf$_reg_info('proc_chnf_callback',lv_qosflags,0,lv_operations_filter,0);

  lv_reg_id := dbms_change_notification.NEW_REG_START(lv_reg_info);

  -- register tables

  select t.col1 into lv_temp from rtm_change_notification t where t.col1 = 'XIECH24'; 

  dbms_change_notification.REG_END;

end;

/

4. Check the registration

select r.* from user_change_notification_regs r;

5. Verify the registration

insert into rtm_change_notification values('1','2',10010 );

commit;

 

select * from nfcount;

6. Deregister

declare

  lv_reg_id number := reg_id;/*the reg_id could be get by query user_change_notification_regs */

begin

  dbms_change_notification.DEREGISTER(lv_reg_id);

end;

/