Oracle handbook 14: Change Notification)

In the process of app development, some data access frequency is very high, but the data does not change much. We usually let it reside in the memory to improve access performance, but this mechanism has a problem, that is how to monitor data changes. The introduction of change notification introduced in Oracle 10 Gb can solve this problem well.To put it simply, change notification means that Oracle can give a notification when the table data you specified changes. We use ODP. NET as an example. First, create an example Table tab_cn and insert data. We hope that the app will receive a notification when the data changes.

 Create   Table Tab_cn (ID Number , Val Number  );  Insert   Into Tab_cn Values ( 1 ,100  );  Insert   Into Tab_cn Values ( 2 , 200  );  Insert   Into Tab_cn Values ( 3 , 300  ); Commit  ; SQL  >   Select T. * , Rowid From  Morven. tab_cn t; Id Val rowid  --  ------------------------------------           1         100  Aaaardaakaademfaaa  2          200 Aaaardaakaademfaab  3          300 Aaaardaakaademfaac

In addition, you must grant the change notification permission to the database user (in this example, Morven:

 
GrantChange NotificationToMorven;
 

Below is the corresponding C #Code(For simple code, exception handling will not be posted ):

 Oracledependency Dep; oracleconnection conn;  //  Public  Mainwindow () {initializecomponent ();  // Set the listening port of the app, that is, the port used to receive change notification.  Oracledependency. Port = 49500  ;  String Cs = "  User ID = Morven; Password = TR; Data Source = MH  "  ; Conn = New  Oracleconnection (CS); Conn. open ();}  //  Private   Void Btreg_click (Object  Sender, routedeventargs e) {oraclecommand cmd = New Oraclecommand ( "  Select * From tab_cn  "  , Conn );  //  Bind an oracledependency instance to an oraclecommand instance  Dep = New  Oracledependency (CMD );  // Specify whether the notification is object-based or query-based. The former indicates that notification is sent when any data changes in the table (tab_cn in this example). The latter provides more fine-grained notification, for example, you can add the WHERE clause in the preceding SQL statement to specify that notification is only applicable to the data in the query results, rather than the entire table.  Dep. querybasednotification = False  ;  //  Whether to include the rowid corresponding to the changed data in notification  Dep. rowidinfo = Oraclerowidinfo. include;  //  Specifies the event handling method after notification is received.  Dep. onchange + = New Onchangeeventhandler (onnotificaton );  //  Whether to remove this registration immediately after one notification  Cmd. Notification. isnotifiedonce = False  ;  //  The expiration time (in seconds) of this registration. If the expiration time is exceeded, the registration will be automatically removed. 0 indicates no timeout.  Cmd. Notification. Timeout = 0  ;  //  False indicates that the notification will be stored in the memory, true indicates that the notification will be stored in the database, and true indicates that the notification will not be lost even after the database is restarted. Cmd. Notification. ispersistent = True  ;  //  Oracledatareader ODR = Cmd. executereader ();  //      This . Rtb1.appendtext ( "  Registration completed.  " + Datetime. Now. tolongtimestring () + Environment. newline );}  Private   Void Btunreg_click ( Object  Sender, routedeventargs e ){  //  Cancel  Dep. removeregistration (conn );  This . Rtb1.appendtext ( "  Registration removed.  " + Datetime. Now. tolongtimestring () + Environment. newline );}  Private   Void Onnotificaton ( Object SRC, oraclenotifeventargs Arg ){  //  You can obtain the specific notification information from Arg. details, such as the rowid of the changed data.  Datatable dt = Arg. details;  //  ......      This  . Rtb1.dispatcher. begininvoke (dispatcherpriority. Normal,  New Action () => {  This . Rtb1.appendtext ( " Notification already ed.  " + Datetime. Now. tolongtimestring () + "  Changed data (rowid ):  " + Arg. Details. Rows [ 0 ] [ "  Rowid  " ]. Tostring () + Environment. newline );}));} 

Click the register button of this app, and then update the tab_cn table on the database side using the following statement:

UpdateTab_cnSetVal=1000 WhereID=1;Commit;
 

In this case, the app receives the notification and obtains the rowid corresponding to the changed data row.Then we deregistered the registration. For output information, see:

Relationship Between Change Notification and Oracle connection

In actual tests, notification still works normally, whether we are using connection. Close () or manually kill the corresponding session in the database or kill the corresponding process (thread) at the OS layer.

That is to say, in addition to initialization and removeregistration, they depend on the corresponding connection. In other cases, they do not have dependency.

Duplicate registration

If the Code has a vulnerability, duplicate registration may occur. In the dba_change_icationication_regs view, multiple duplicate records (with different regids) are displayed, and more than 100000 records have been recorded.

In the above app, if I click the register button multiple times, it will lead to repeated registration. One of the consequences of repeated registration is that the app will receive multiple Identical notifications when the data changes.

The other consequence of repeated registration is much more serious, resulting in a delay in the commit of the updated table (tab_cn in this example. When 10000 is registered repeatedly, it takes about one minute for commit to update a record in the tab_cn table. It also affects the speed of database shutdown or startup, because both actions will send a notification (the notification content is blank ).

I personally think that Oracle should put an end to this situation from the inside, because the significance of repeated registration remains to be discussed.Below I will slightly modify the code to avoid repeated registration issues.

 If (DEP = Null |!Dep. isenabled) {oraclecommand cmd = New Oraclecommand ( "  Select * From tab_cn  "  , Conn); Dep = New  Oracledependency (CMD); dep. querybasednotification = False  ; Dep. rowidinfo = Oraclerowidinfo. Include; dep. onchange + = New Onchangeeventhandler (onnotificaton );  //  Cmd. Notification. isnotifiedonce = False  ; Cmd. Notification. Timeout = 0  ; Cmd. Notification. ispersistent = True  ;  //  Oracledatareader ODR = Cmd. executereader ();  This . Rtb1.appendtext ("  Registration completed.  " + Datetime. Now. tolongtimestring () + Environment. newline );} 

I added a judgment here. First, judge whether the oracledependency instance is null (that is, click the register button for the first time), and then judge oracledependency. isenabled. This attribute is set to false in the following situations: 1) It has been initialized but the command has not been executed; 2) the timeout set at registration expires; 3) It has been deregistered by removeregistration, note that removeregistration does not cause the oracledependency instance dispose. The modified code can be registered only when the user clicks register for the first time or unregister before.

ClearDba_change_icationication_regsRecord

We used oracledependency. the removeregistration method is used to cancel a registration, but the app crashes and exits before it can be logged out. In this case, the dba_change_icationication_regs record is not cleared manually, but normally, when you update the corresponding data table (tab_cn in this example) and commit it, Oracle will automatically clear the records because Oracle has detected that these registration has expired, but sometimes it will not be completely cleared immediately. If there is a delay, Oracle seems to be clearing a batch of data.

MultipleAppRegister the same port

As mentioned above, we can register multiple times in the same app. However, if different apps are registered on the same port (49500 in this example, the ORA-24912: Listener thread failed will occur. listen failed exception.