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.
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
<dependencies>
<!-- Oracle JDBC Driver -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ojdbc11</artifactId>
<version>21.5.0.0</version> <!-- Use a recent
version -->
</dependency>
<!-- Oracle Notification Service (ONS)
- required for notifications -->
<dependency>
<groupId>com.oracle.database.jdbc</groupId>
<artifactId>ons</artifactId>
<version>21.5.0.0</version>
</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.
***************************************
