Thursday, 7 January 2010

Closing Obsolete Database Change Notification Registrations

When enabling Database Change Notification (DCN) a registration is created at the database level which requires it to closed when no longer required. Here is how to close an obsolete registration from JDBC in a case where you don't have a valid DatabaseChangeRegistration object (for example a JVM crash).

1. Find the
obsolete registration as shown below.


SCOTT@linux11g> select regid, CALLBACK, table_name from USER_CHANGE_NOTIFICATION_REGS;

REGID CALLBACK TABLE_NAME
---------- ---------------------------------------- --------------------
100 net8://(ADDRESS=(PROTOCOL=tcp)(HOST=10.1 SCOTT.DEPT
87.80.135)(PORT=47632))?PR=0

2. Use JDBC code as follows to delete the obsolete registration.

package pas.jdbc;

import java.sql.SQLException;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

public class DeregisterDCN
{
private OracleDataSource ods = null;
public final String userId = "scott";
public final String password = "tiger";
public final String url = "jdbc:oracle:thin:@beast.au.oracle.com:1521:linux11g";
private int REGID = 100;

public DeregisterDCN() throws SQLException
{
ods = new OracleDataSource();
ods.setUser(userId);
ods.setPassword(password);
ods.setURL(url);
}

public void run () throws SQLException
{
try
{
OracleConnection conn = getConnection();
conn.unregisterDatabaseChangeNotification(REGID);
System.out.println("unregistered with id " + REGID);
}
catch (SQLException sqle)
{
System.out.println("Error trying to delete registration with id " + REGID);
sqle.printStackTrace();
}
}

public OracleConnection getConnection() throws SQLException
{
OracleConnection conn = (OracleConnection)ods.getConnection();

return conn;
}

public static void main(String[] args) throws SQLException
{
DeregisterDCN deregisterDCN = new DeregisterDCN();
deregisterDCN.run();
}
}
Few things to note though..

1. The database user has been granted the following as user SYS to enable DCN.

> grant change notification to scott;

> grant execute on DBMS_CQ_NOTIFICATION to scott;

2. The method to use is OracleConnection.unregisterDatabaseChangeNotification(int). That requires you to cast a JDBC Connection to an OracleConnection to use that method as shown below.

OracleConnection conn = (OracleConnection)ods.getConnection();

3. They may be more then one registration so make sure you determine the correct entry to remove. If not you may end up deleting a registration which is currently active.


4 comments:

asmol said...

Pas

good to see you kicking some Java ass :)
Like your blog title as well.
Keep on kicking it man we need people like you in the field.

Cheers

Toly

lava said...

But where do I get DBMS_CQ_NOTIFICATION?

Pas Apicella said...

The PLSQL package is described here. It's part of 11g, it's not actually required for this demo but is used for PLSQL and change notification.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_cqnotif.htm

lava said...

Oh it is available only for 11GR1