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;
/