Overview on Database Change Notification in Oracle10g

Oracle Database Change Notification Service enables applications to receive notification alerts whenever there is a change in the result set of a query of a SQL statement. It allows client applications to register queries with the database and obtain notifications when a DML (Data Manipulation Language) or DDL(Data Definition Language) change occurs on any of the objects associated with the queries. The notifications are published by the database on the event of the DML or DDL transactions are committed.

Using this service the application can specify a notification request for the execution of a statement and create a notification registration using the notification framework. When there is a change in the query result set the application is correspondingly notified about the change by the notification framework.

Based on this notification the application can take appropriate action. For example Database change notification feature is useful in mid-tier applications that depend on cached data. Cached data becomes outdated when a transaction occurs on the underlying database object.

If the application uses Database Change Notification feature, then the database can circulate a notification when a change occurs to the registered object with details on the changes. The application can in response refresh the cached data if necessary and make it up to date.

The change notification issued by the database can contain information such as names of the modified objects, the type of change (caused by INSERT, UPDATE or DELETE), the ROWID’s of the changed rows etc. To obtain the values for the changed rows the database must be queried based on the information in the notification.

In order to create a registration, it is mandatory for the user to have the CHANGE NOTIFICATION system privilege. In addition the user needs to have SELECT privilege on all objects that are to be considered for registration even after the creation of registration is completed.

A query can be registered to receive notifications by performing the following actions. First the notification recipient for the queries to be registered is created. The notification recipient can be a PL/SQL stored procedure or OCI (Oracle Call Interface) callback function.

Next a query registration for the specified notification recipient is created. This is done by execution of SQL queries. The Change Notification Registrations are continual by default and are present until the application explicitly removes registration. After successful creation of registration, the Oracle Database notifies recipient in response to the changes to objects referred to in the registered queries when the primary transaction is committed.

The application can register most query types except queries on fixed tables or views, queries with dblinks inside them and queries over materialized views. Registration is based on the version and definition of the object at the time of registration of query. When multiple registered objects are changed on a single transaction the application will receive one notification per modified object as soon as the transaction is committed.

The registration properties or the options supported by Oracle for an object registration are Purge on Notify option (removal of registration after first change notification), Timeout option (expiration of registration after time interval), ROWID’s option (ROWID’s of changed rows as part of notification), reliable notification option (notifications are sent in a persistent database queue instead of shared memory to survive instance crashes), operations filter (notification only for certain operations) and Transaction Lag (to specify a count between successive notifications).

Registration interfaces are obtainable in both PL/SQL and OCI. The PL/SQL API (Application Programming interface) enables user to define a registration block. Similarly the OCI Registration API involves the function OCISubsrciptionRegister to be invoked.

If a PL/SQL stored procedure is created as the change notification recipient,
the registration of queries for notification through PL/SQL can be done either by creation of a CHNF$_REG_INFO object that specifies the call back procedure’s name and other information containing the notification or by execution of a program unit in the DBMS_CHANGE_NOTIFICATION package along with execution of queries to be registered.

The Data Dictionary views DBA_CHANGE_NOTIFICATION_REGS and USER_CHANGE_NOTIFICATION_REGS can be queried to obtain information about registered clients.

For best performances of Change notification, guidelines to keep in mind are as follows. User’s need to avoid registration on objects that are extremely volatile as it will cause a large number of notifications to be sent and can reduce the OLTP throughput owing to the overhead of generating notifications . Also duplication of registration on the same object is to be avoided to keep the same notification messages from being sent to multiple recipients

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |