Oracle change notification

Change Notification is an oracle feature that generates real time alerts when data in the tables registered with the change notification process is modified. These alerts can be used by applications thus enabling them to trigger a suitable action based on the occurrence of the dml or ddl event on that table.

Change notification is managed by the DBMS_CHANGE_NOTIFICATION package,  which registers the call back procedure and the required tables with change notification.

The call back procedure is where you can capture the required dml or ddl events on the relevant tables and take the desired action.

This call back procedure can either be a PLSQL procedure or a client side application program like a C or JAVA callback. The notification handler is invoked whenever a transaction changes any of the registered objects and commits.

To use this functionality we must make sure that the JOB_QUEUE_PROCESSES initialization parameter is set to a non zero value. 

In addition to this, the schema user using change notification must have the CHANGE NOTIFICATION privilege and execute privileges on the DBMS_CHANGE_NOTIFICATION package.  

Below are the sample scripts to be executed for enabling CHANGE NOTIFICATION on CUST schema.

For our example let us assume that the application wants to be notified whenever a dml operation happens of the CUSTOMER table.

The application can register the CUSTOMER table with the database change notification feature. If a user adds, modifies or deletes a CUSTOMER record from this table the application will receive a change notification.

When the database issues a change notification, following information is available to the application callback procedure

• The name of the object changed.
• The type of dml or ddl operation like an INSERT, UPDATE, DELETE or an ALTER command on the table.
• The rowid of the rows changed.
• For global events on the database like a STARTUP or a SHUTDOWN, the database delivers a notification when the first instance on the database starts or the last instance shuts down.

To enable change notification to a particular oracle schema user, we need to give the following grants.

C:\Users\winni>sqlplus sys as sysdba
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=xepdb1;

Session altered.

SQL> GRANT CHANGE NOTIFICATION TO CUST;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO CUST;

Grant succeeded.

The job_queue_processes database parameter should be set to a value greater than 0
SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     80

We create the callback procedure for the change notification process as follows.

CREATE OR REPLACE PROCEDURE chnf_repl_callback(ntfnds IN SYS.CHNF$_DESC) IS
   regid           NUMBER;
   tbname          VARCHAR2(60);
   event_type      NUMBER;
   numtables       NUMBER;
   operation_type  NUMBER;
   numrows         NUMBER;
   row_id          VARCHAR2(20);
 BEGIN
     regid      := ntfnds.registration_id;
     numtables  := ntfnds.numtables;
     event_type := ntfnds.event_type;
 
  
  IF (event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE) THEN
    FOR i IN 1..numtables LOOP
      tbname          := ntfnds.table_desc_array(i).table_name;
      operation_type  := ntfnds.table_desc_array(I). Opflags;
      tbname := SUBSTR(tbname,INSTR(tbname,'.')+1);

      
      /* To get the rowids of table rows changed, obtain them as follows */
      IF (bitand(operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
        numrows := ntfnds.table_desc_array(i).numrows;
      ELSE 
        numrows :=0;   /* ROWID INFO NOT AVAILABLE */
      END IF;
      
      /* The body of the loop is not executed when numrows is ZERO */
      FOR j IN 1..numrows LOOP
          Row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;
          INSERT INTO REPL_QUEUE VALUES (tbname, Row_id, 'PNDNG', DECODE(operation_type, 2,'INSERT', 4, 'UPDATE', 8, 'DELETE'));
         
      END LOOP;
      
    END LOOP;
  END IF;
  COMMIT;
END;
/

We make the plsql block to register the callback procedure and the required tables with change notification.

CREATE OR REPLACE PROCEDURE REGISTER_CALLBACK AS
  REGDS      SYS.CHNF$_REG_INFO;
  regid      NUMBER;
  cnt        NUMBER;
  qosflags   NUMBER;
BEGIN
 qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE +
         DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
REGDS := SYS.CHNF$_REG_INFO ('chnf_repl_callback', qosflags, 0,0,0); -- register the callback procedure to change notification
regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (REGDS); 
SELECT count(1) INTO cnt FROM CUSTOMER WHERE 1=2; -- register the CUSTOMER table for change notification
DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/

We register the callback procedure and the required tables with change notification by running the above block as follows

BEGIN
  REGISTER_CALLBACK();
  COMMIT;
--rollback; 
END;

Check in user_change_notification_regs table if registration has happened successfully.

SELECT regid,
       table_name,
       callback
FROM   user_change_notification_regs;

REGID	TABLE_NAME	CALLBACK
305	CUST.CUSTOMER	plsql://chnf_repl_callback?PR=0

Once the table and callback procedure are registered with change notification, we can test the same by inserting, updating, deleting from the CUSTOMER table

INSERT INTO CUSTOMER (CUST_ID, CUST_NAME, PHONE_NUMBER, EMAIL, ADDR_LINE_1, ADDR_LINE_2, ADDR_LINE_3, COUNTRY_ID, REGION_ID, GENDER) VALUES ('100017', 'NOVAK DJOKOVIC-100017', '+381 (0)11 3148648', 'djoko@gmail.com', 'Bulevar Arsenija Carnojevica 54a100016', '11070 New Belgrade', 'Republic of Serbia', 'SR', '3', 'MALE');

We now select from REPL_QUEUE

SELECT * FROM REPL_QUEUE;

TBL_NAME	UNIQUE_ID	REPL_STAT	OPRTN
CUST.CUSTOMER	AAAUR/AAUAAAAHGAAQ	PNDNG	INSERT

Update CUSTOMER table

UPDATE CUSTOMER
SET EMAIL = 'djoko17@gmail.com'
WHERE CUST_ID = 100017;

Select and check for update operation

TBL_NAME	UNIQUE_ID	REPL_STAT	OPRTN
CUST.CUSTOMER	AAAUR/AAUAAAAHGAAQ	PNDNG	UPDATE

Delete from customer table.

DELETE FROM CUSTOMER WHERE CUST_ID = 100017;

Select and check in REPL_QUEUE for delete operation.

TBL_NAME	UNIQUE_ID	REPL_STAT	OPRTN
CUST.CUSTOMER	AAAUR/AAUAAAAHGAAQ	PNDNG	DELETE

To deregister a particular id with the corresponding tables and call back procedure from change notification, we run the following

execute DBMS_CHANGE_NOTIFICATION.deregister(305);

To add an object to an existing Change Notification Registration

Select the existing reg_id from user_change_notification_regs table and assign this id in the below PLSQL block to num_reg_id.

CREATE OR REPLACE PROCEDURE REG_OBJ_WT_CN(p_reg_id  NUMBER, p_table_name  VARCHAR2)
AS
  
  v_sql  VARCHAR2(1000);
  v_dummy  VARCHAR2(100);

BEGIN
  -- begin registration boundary
  DBMS_CHANGE_NOTIFICATION.ENABLE_REG(p_reg_id);
    
   v_sql := 'SELECT COUNT(1) FROM ' || USER || '.' || p_table_name || ' WHERE rownum<2';
   
   EXECUTE IMMEDIATE v_sql INTO v_dummy ;
    
    DBMS_CHANGE_NOTIFICATION.REG_END;
END;
/

Now if we select from user_change_notification_regs table we will see CUSTOMER and DAILY_TRANSACTIONS table entries.

SELECT regid,
       table_name,
       callback
FROM   user_change_notification_regs;

REGID	TABLE_NAME	CALLBACK
305	CUST.CUSTOMER	plsql://chnf_repl_callback?PR=0
305	CUST.DAILY_TRANSACTIONS	plsql://chnf_repl_callback?PR=0

Uses of Database Change Notification

There are a number of uses for database change notification like

Refreshing mviews on a remote database.

Maintaining audit trails and data replication on another schema.

Caching of query results on the client - for instance, an application can create a registration on the queries it is interested in caching using the change notification service. When the database issues the change notification, the application can refresh its cache by re executing the queries.

For example - a web forum application which is intrinsically tolerant of slightly out of date data, can benefit from caching in the middle tier. Database change notification can help in keeping the cache updated with the backend database.

Best Practices

For optimum usage of change notification following guidelines to be followed

Only few objects should be registered with change notification.

Modifications/Inserts to these objects should be few. If there are too many dml operations like bulk operations on the table, it can slow down OLTP throughput due to the overhead of generating notifications.

Duplicate registrations for an object should be avoided as far as possible as it will cause the same notification message to be replicated to multiple recipients.

Troubleshooting

Make sure CHANGE NOTIFICATION privilege and execute privileges on the DBMS_CHANGE_NOTIFICATION package are given to the schema user.

Make sure JOB_QUEUE_PROCESSES parameter is having value greater than 0.

For issues in change notification can refer to

Troubleshooting Change Notifications Issues

References 

https://oracle-base.com/articles/10g/dbms_change_notification_10gR2 https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_chngnt.htm#CIHFIIHC

What Is Database Change Notification?

Thought for the day

In great and small matters cause no harm

  and do not become an enemy instead of a friend

for a bad name incurs shame and reproach

  so it is with the double-tongued sinner.

Sirach 5:15