Database-Box: Oracle Table DDL Change Notification Feature

Oracle Table DDL Change Notification, which is part of a broader Oracle technology called Continuous Query Notification (CQN)

History:

One of traditional solutions to controlling schema changes without interruptions in oracle to releasing and developing applications backend is Edition Based Redefinition (EBR) feature that was available from 11g.

Before explanations of TDCN, is better to have a review on EBR:

Overview of Edition-Based Redefinition

To upgrade an application while it is in use, you must copy the database objects that comprise the database component of the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they can continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.

Using EBR means using one or more of its component features. The features you use, and the downtime, depend on these factors:

What kind of database objects you redefine
How available the database objects must be to users while you are redefining them
Whether you make the upgraded application available to some users while others continue to use the older version of the application

You always use the edition feature to copy the database objects and redefine the copied objects in isolation; that is why the procedure that this chapter describes for upgrading applications online is called edition-based redefinition (EBR).

If every object that you will redefine is editioned, then the edition is the only feature you use.

Tables are not editioned objects. If you change the structure of one or more tables, then you also use the editioning view feature.

If other users must be able to change data in the tables while you are changing their structure, then you also use forward crossedition triggers. If the pre- and post-upgrade applications will be in ordinary use at the same time (hot rollover), then you also use reverse crossedition triggers. Crossedition triggers are not a permanent part of the application—you drop them when all users are using the post-upgrade application.

An EBR operation that you can perform on an application in one edition while the application runs in other editions is a live operation.

For more info you can visit:

https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/editions.html#GUID-C213A6AA-5427-4426-80B5-D5301386253E

https://docs.oracle.com/en/database/oracle/oracle-database/26/adfns/editions.html#GUID-F0D940E0-618D-4656-982E-1C5E49FCCD42

But in this topic, I will present a new feature to alerting to the application when a DDL change occurred! This can help obviously be helpful when we have a mid-layer cache in application layer that cached Schema objects structure on startup and any change in table structure can lead a downtime.

v But in this topic, I will introduce a new feature to send an alert to the application when a DDL change occurs! This feature can be clearly useful when we have a middle-tier cache in the application layer that stores the structure of Schema objects at startup and any change in the table structure can lead to a crash.

Oracle introduce a feature called to Table DDL Change Notification to handle these changes, by this solution your application be aware for any schema changes dynamically.

In simple terms, this feature allows an application (like a Java, C#, or Python program) to be automatically notified by the database whenever the structure (the DDL) of a specific table changes, this feature is available for both OCI and On-Premises deployments.

Overview of Table DDL Change Notification

Table DDL Change Notification provides Oracle Call Interface (OCI) clients an efficient mechanism to subscribe for DDL notifications on tables of interest. Table DDL Change Notification ensures that applications are notified when DDL statements make changes to a table. Changes are captured as DDL events and these events are processed asynchronously without blocking the user DML activity.

Applications seeking table metadata can use Table DDL Change Notification instead of continuously polling for DDL changes, and benefit from reduced network round trips. Table DDL Change Notification is also useful for OCI clients of the database that cache table metadata in the middle tier to avoid round trips to the database.

An OCI client can register to receive table DDL change notification for any of the following:

A list of tables
A list of schemas
All tables in a database

Table DDL Change Notification is included in Oracle AI Database 26ai, and later releases.

Benefits of Table DDL Change Notification

Table DDL Change Notification works without affecting the application activity in the database.

The following are some of the benefits of using Table DDL Change Notification:

Notifications can be enabled on highly active tables.
Registering and event processing do not block the DDL activity on a table.
Registrations and notifications are processed with minimal overhead on DML activity and without invalidating table cursors.
Registrations are processed quickly while allowing concurrent registrations of other client applications.
Client applications can dynamically subscribe to any number of additional tables or schemas.

Features of Table DDL Change Notification

The following are the features of Table DDL Change Notification:

DDL events are processed asynchronously without blocking user DML queries.
The client application can choose to include optional events, such as Partition Maintenance Operations (PMOP) or truncate, which are not available for event registration by default.
DDL notifications are staged in the System Global Area (SGA). If an instance restarts, unprocessed notifications in SGA are lost. Persistent events are not supported.
On failover of the database, any undelivered events in SGA are lost. The client caches must be invalidated on failover to avoid reconciliation issues in the table metadata due to lost notifications.
For logical standby and physical standby, the OCI client must register again over the new primary database to resume notification.

What is the Core Problem it Solves?

Imagine you have an application that caches data from an Oracle table to improve performance. For example, a web application might load a list of product categories from a PRODUCT_CATEGORIES table into its memory when it starts up.

Now, what happens if a Database Administrator (DBA) does one of the following things to that table?

ALTER TABLE PRODUCT_CATEGORIES ADD (IS_FEATURED VARCHAR2(1)); (Adds a new column)
ALTER TABLE PRODUCT_CATEGORIES MODIFY (CATEGORY_NAME VARCHAR2(200)); (Changes a column's size)
ALTER TABLE PRODUCT_CATEGORIES DROP COLUMN OBSOLETE_FLAG; (Drops a column)
TRUNCATE TABLE PRODUCT_CATEGORIES; (Deletes all data instantly)
DROP TABLE PRODUCT_CATEGORIES; (Deletes the entire table)

The application's in-memory cache is now stale and incorrect. It doesn't know about the new IS_FEATURED column, or it might crash trying to insert a 150-character category name into a field it thinks is still only 100 characters long. The application is now out of sync with the database schema.

Without this feature, the only way to fix this is to restart the application or build a complex, manual process to signal a refresh.

How Does Table DDL Change Notification Solve This?

This feature provides an elegant, push-based solution. Instead of the application constantly checking if the table has changed (polling), the database itself tells the application when a change happens.

The Application Registers its Interest:

o The application (through a database driver like JDBC for Java) creates a special "notification registration" object.

o It tells the database, "I am interested in any DDL changes made to the SCOTT.EMP table."

o It also provides a "callback" method—a function in the application's code that the database driver should execute when a notification arrives.

A DDL Change Occurs:

o A DBA or another process executes a command like: ALTER TABLE SCOTT.EMP ADD (COMMISSION NUMBER);

Ø The Database Sends a Notification:

o The Oracle database detects this DDL change on the SCOTT.EMP table.

o It sees that an application has registered interest in this specific event.

o The database generates a notification message and sends it over the network to the application's database driver. This message contains details about what happened (e.g., EVENT_TYPE=ALTER, TABLE_NAME=EMP).

The Application Reacts:

o The application's database driver receives the notification.

o It automatically invokes the "callback" function that the application registered in Step 1.

o Inside this function, the application developer writes code to handle the event. The typical action is to invalidate the cache. The application would discard its old, stale copy of the EMP table's structure and data, and then re-query the database to fetch the fresh information.

Registering for Table DDL Change Notification

A client can register for DDL change notifications at the table level or the schema level (for schema-wide table DDL events).

To register for notifications, you must fulfill the following conditions:

You must be a non-SYS user.
You must have SELECT ANY TABLE privileges.
You must have CHANGE NOTIFICATION privileges.
Set up a TCP or IPC listener for OCI DDL Notification client connections, and set the local_listener parameter of the ROOT container to this listener. The local_listener parameter can be set in the initialization parameter file (init.ora), or as follows:

ALTER SYSTEM SET LOCAL_LISTENER=listener_name;

ALTER SYSTEM REGISTER;

Note:If a user loses the required privileges, the user's table or schema-level registrations are implicitly unregistered.

Monitoring Table DDL Change Notification

The registration identifier (ID) uniquely identifies a subscription. A client can register on multiple tables or schemas in the database using the same subscription (meaning, registration ID). The following views can be queried by the registration ID to monitor table DDL events received for a subscription.

DBA_DDL_REGS for information on all table, schema or database-level registrations.
DBA_SUBSCR_REGISTRATIONS for more information about subscriptions created in the database.
V$SUBSCR_REGISTRATION_STATS (V_SUBSCR_REGISTRATION_STATS) for notification statistics and diagnostic information about a subscription.

Step-by-step guide to implementing and using the Table DDL Change Notification feature in Oracle.

This guide will walk you through:

Setting up the database user and permissions.
Creating a sample table to monitor.
Writing a complete Java application that registers for notifications and reacts to them.
Triggering a DDL change and observing the application's response.

We will use Java with the Oracle JDBC driver, as it's the most common platform for this feature.

Step 1: Database Setup (as a DBA)

First, you need a database user with the necessary privileges. It's best practice not to use SYS or SYSTEM for this.

Create a dedicated user. Let's call our user app_user.

-- Connect as SYS or a user with DBA privileges

CREATE USER app_user IDENTIFIED BY YourStrongPassword;

ALTER USER app_user QUOTA UNLIMITED ON users;

Grant necessary privileges. The application user needs basic connection privileges plus the special CHANGE NOTIFICATION privilege, which is the key to this entire feature.

-- Grant connection and resource creation rights

GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO app_user;

-- *** This is the most important grant for this feature ***

GRANT CHANGE NOTIFICATION TO app_user;

Step 2: Create the Target Table (as app_user)

Now, connect as the new app_user and create the table that your application will monitor.

Connect as app_user in SQL*Plus, SQL Developer, or your favorite SQL tool.
Create a simple table. We'll create an EMPLOYEES table.

CREATE TABLE employees (

emp_id NUMBER PRIMARY KEY,

emp_name VARCHAR2(100),

hire_date DATE

);

-- Insert some sample data (optional, but good for context)

INSERT INTO employees (emp_id, emp_name, hire_date) VALUES (101, 'Alice', SYSDATE);

COMMIT;

At this point, the database is ready. The app_user owns a table called employees and has the permission to register for notifications on it.

Step 3: Create the Java Application

This is the core of the implementation. We will write a Java program that connects to the database, registers a listener on the employees table, and then waits indefinitely for notifications.

Prerequisites:

Java Development Kit (JDK) 8 or higher.
An IDE like IntelliJ IDEA, Eclipse, or VS Code.
The Oracle JDBC driver (ojdbc8.jar or higher) and the Notification driver (ons.jar). If you use a build tool like Maven, this is much easier.

Maven Dependencies (Recommended)

If you're using Maven, add these dependencies to your pom.xml file. This is the easiest way to manage the required JARs.

XML

<groupId>com.oracle.database.jdbc</groupId>

<artifactId>ojdbc11</artifactId>

<version>21.5.0.0</version> 

</dependency>

<groupId>com.oracle.database.jdbc</groupId>

</dependency>

</dependencies>

Java Code (DDLNotificationListener.java)

Here is the complete, commented Java code.

Java

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Properties;

import oracle.jdbc.OracleConnection;

import oracle.jdbc.OracleStatement;

import oracle.jdbc.dcn.DatabaseChangeEvent;

import oracle.jdbc.dcn.DatabaseChangeListener;

import oracle.jdbc.dcn.DatabaseChangeRegistration;

import oracle.jdbc.dcn.TableChangeDescription;

public class DDLNotificationListener {

// --- Configuration: Change these to match your database ---

private static final String DB_URL = "jdbc:oracle:thin:@//your_db_host:1521/your_service_name";

private static final String DB_USER = "app_user";

private static final String DB_PASSWORD = "YourStrongPassword";

public static void main(String[] args) {

try {

new DDLNotificationListener().run();

} catch (Exception e) {

System.err.println("An error occurred: " + e.getMessage());

e.printStackTrace();

}

private void run() throws Exception {

System.out.println("Connecting to the database...");

OracleConnection conn = connect();

// 1. Create Notification Registration Properties

Properties props = new Properties();

// We are interested in DDL changes, not DML (data changes)

props.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "false");

props.setProperty(OracleConnection.DCN_NOTIFY_DDL, "true"); // <-- The key property!

// 2. Create a Database Change Registration

DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(props);

System.out.println("Created database change registration.");

try {

// 3. Add a listener to the registration

MyDDLListener listener = new MyDDLListener();

dcr.addListener(listener);

System.out.println("Added a listener to the registration.");

// 4. Associate the registration with the target table

// We do this by creating a statement and associating it with the registration.

// The query itself is just a dummy to specify the table we're watching.

try (Statement stmt = conn.createStatement()) {

((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);

try (ResultSet rs = stmt.executeQuery("SELECT * FROM employees")) {

// We don't need to process the result set.

// The act of executing the query on the registered statement

// is what links the 'employees' table to our notification.

}

System.out.println("Associated the 'employees' table with the notification listener.");

System.out.println("----------------------------------------------------------");

System.out.println("Application is now running. Waiting for DDL changes on the EMPLOYEES table...");

System.out.println("Go to your SQL tool and run an ALTER, TRUNCATE, or DROP command on the table.");

// 5. Keep the application alive to listen for notifications

// In a real app, this would be a server or a background thread.

while (true) {

Thread.sleep(5000);

}

} finally {

// In a real app, you would unregister and close the connection gracefully.

System.out.println("Unregistering notification and closing connection.");

conn.unregisterDatabaseChangeNotification(dcr);

conn.close();

}

private OracleConnection connect() throws Exception {

return (OracleConnection) java.sql.DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

}

// Inner class that defines what to do when a notification is received

class MyDDLListener implements DatabaseChangeListener {

@Override

public void onDatabaseChangeNotification(DatabaseChangeEvent event) {

System.out.println("\n!!! NOTIFICATION RECEIVED !!!");

System.out.println("Event Details: " + event.toString());

// Get specific details about the table DDL change

TableChangeDescription[] tableChanges = event.getTableChangeDescription();

if (tableChanges != null && tableChanges.length > 0) {

for (TableChangeDescription change : tableChanges) {

System.out.println(" -> Table Name: " + change.getTableName());

System.out.println(" -> DDL Operation Type: " + change.getDDLOperationType());

// --- THIS IS WHERE YOU WOULD PUT YOUR APPLICATION LOGIC ---

// For example: invalidate a cache, reload configuration, etc.

System.out.println(" -> ACTION: Invalidating cache for " + change.getTableName());

}

System.out.println("----------------------------------------------------------");

System.out.println("Resuming wait for next notification...");

}

Step 4: Run the Application and Trigger the Notification

Compile and Run the Java Application. Execute the main method of the DDLNotificationListener class. You should see the following output in your console, and then the program will pause, waiting:

Connecting to the database...

Created database change registration.

Added a listener to the registration.

Associated the 'employees' table with the notification listener.

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

Application is now running. Waiting for DDL changes on the EMPLOYEES table...

Go to your SQL tool and run an ALTER, TRUNCATE, or DROP command on the table.

Trigger a DDL Change. Go back to your SQL tool (connected as app_user or another user with privileges on the table) and execute a DDL command on the employees table.

Example 1: Add a column

ALTER TABLE employees ADD (department_id NUMBER);

Observe the Java Application's Output. Almost immediately after you execute the ALTER TABLE command, you will see new output in your Java application's console. This is the database pushing the notification to your running program.

!!! NOTIFICATION RECEIVED !!!

Event Details: oracle.jdbc.dcn.DatabaseChangeEvent[CONNECTION_ID=... EVENT_TYPE=DCN_EVENT_TYPE_DDL]

-> Table Name: APP_USER.EMPLOYEES

-> DDL Operation Type: ALTER

-> ACTION: Invalidating cache for APP_USER.EMPLOYEES

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

Resuming wait for next notification...

Try another DDL command. Now, truncate the table.

TRUNCATE TABLE employees;

Again, the Java application will instantly receive a notification:

!!! NOTIFICATION RECEIVED !!!

Event Details: oracle.jdbc.dcn.DatabaseChangeEvent[CONNECTION_ID=... EVENT_TYPE=DCN_EVENT_TYPE_DDL]

-> Table Name: APP_USER.EMPLOYEES

-> DDL Operation Type: TRUNCATE

-> ACTION: Invalidating cache for APP_USER.EMPLOYEES

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

Resuming wait for next notification...

This completes the end-to-end process. You have successfully configured the database and built an application that can react in real-time to structural changes in a database table.

***************************************

Database-Box

Monday, April 13, 2026

Oracle Table DDL Change Notification Feature

No comments:

Post a Comment

Oracle Table DDL Change Notification Feature

Report Abuse

Labels