Unanswered: Specified registration id does not exist
Hello Oracle Community
My problem is that my Java application suddenly gets an error. (ORA-29970: Specified registration id does not exist) The source code already worked smoothly. I fear that many of my DataBaseChangeNotification - Registrations in testing mode crashed the database. But I have almost the same code in C#, and the registration is working properly.
My target is setting a trigger to the db to listen on an insert, update, or delete-command. In summary: in C# it works, but not in java. At least it have to be a java application.
System.out.println("driver loaded successfully!");
//jdbc:oracle:thin:@ IP :1521: SID" , USER, PW
conn = (oracle.jdbc.driver.OracleConnection) DriverManager.getConnection(dbConst.connstr, dbConst.usr, dbConst.pwd);
final Properties dbProperties = new Properties();
DatabaseChangeRegistration dbChangeRegistration = conn.registerDatabaseChangeNotification(dbProperties);
DBListener listener = new DBListener();
OracleStatement cmd = (OracleStatement)conn.createStatement();
//select Table for Listener
String cmdtext = "select * from TB";
//associate the statement with the registration
Investigating more about the error I mentioned, I found a pattern when the error is seen. If the application registers for database change notification and aborts before un-registering, the next attempt to register would fail with "ORA-29972: user does not have privilege to change/ create registration" and further attempts will fail with an error "ORA-29970: Specified registration id does not exist".
A real example of the above situation where the application aborts before un-registering could be power failures or network failures. I think I had such a network fail during my app testing.
In addition to above, I also observed that when such a scenario occurs the registrations are purged by the database server after a particular amount of time. Can someone throw some insights on what is the time interval after which the database server purges the invalid registrations? Or, how can I manually delete the registration in the DB. Cause there are no entries in the "DBA_CHANGE_NOTIFICATION_REGS" ...