ORACLE提供的Databas Change Notification特性让我们可以通过Register的形式告诉数据 库，

用户对某些表的内容改变感兴趣。最常用的地方是，在数据发生变化后，刷新Mid-Tier的数据

Cache. 摘录一段文档内容

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

Database Change Notification is a feature that enables client applications to register

queries with the database and receive notifications in response to DML or DDL

changes on the objects associated with the queries. The notifications are published by

the database when the DML or DDL transaction commits.

During registration, the application specifies a notification handler and associates a set

of interesting queries with the notification handler. A notification handler can be either

a server side PL/SQL procedure or a client side C callback. Registrations are created

on all objects referenced during the execution of the queries. The notification handler

is invoked when a transaction subsequently changes any of the registered objects and

commits.

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

给出分别使用PL/SQL和Python的例子

PL/SQL

使 用的对象以及关系

    sys.chnf$_desc <-- sys.chnf$_tdesc <-- sys.chnf$_rdesc

SQL> desc sys.chnf$_desc

Element          Type                  

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

REGISTRATION_ID  NUMBER                

TRANSACTION_ID   RAW(8)                

DBNAME           VARCHAR2(30)          

EVENT_TYPE       NUMBER                

NUMTABLES        NUMBER                

TABLE_DESC_ARRAY SYS.CHNF$_TDESC_ARRAY

 

SQL> desc sys.chnf$_tdesc

Element        Type                  

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

OPFLAGS        NUMBER                

TABLE_NAME     VARCHAR2(64)          

NUMROWS        NUMBER                

ROW_DESC_ARRAY SYS.CHNF$_RDESC_ARRAY

 

SQL> desc sys.chnf$_rdesc

Element Type           

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

OPFLAGS NUMBER         

ROW_ID  VARCHAR2(2000)

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

下 面提供一个简单的例子（首先满足系统权限要求）

CONNECT / AS SYSDBA;

GRANT CHANGE NOTIFICATION TO T2;

GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO T2;

-- 如果有必要，设置参数job_queue_processes为非零（默认就是非零）

ALTER SYSTEM SET job_queue_processes=4;

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

-- create sample data

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

create table ntfn_batch as select rb.* from rps_batch rb where rb.id = 2730006;

create table ntfn_trans as

select rt.*

  from rps_transaction rt

 where rt.original_rps_batch_id = 2730006

 order by rt.id;

 

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

-- create message table

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

drop table ntfn_msg;

create table ntfn_msg

(

  ora_trans raw(8),

  tab_name  varchar2(30),

  row_id    varchar2(2000),

  dt        date default sysdate

);

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

-- create procedure to process the callback when notification occurs

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

/*

You can create a PL/SQL stored procedure that the database server invokes in

response to a change to a registered object. The procedure that receives the notification

must have the following signature, where schema_name is the name of the database

schema and proc_name is the name of the stored procedure:

PROCEDURE schema_name.proc_name( ntfnds IN SYS.CHNF$_DESC )

经过测试，这里的参数名称都 必须一致 ntfnds

*/

create or replace procedure ntfs_callback(ntfnds in sys.chnf$_desc) is

  l_event_type   number;

  l_ora_trans_id raw(8);

  l_numtables    number;

  l_op_flag      number;

  l_tab_name     varchar2(100);

  l_numrows      number;

  l_row_id       varchar2(2000);

begin

  l_event_type   := ntfnds.event_type;

  l_ora_trans_id := ntfnds.transaction_id;

  l_numtables    := ntfnds.numtables;

  -- only concern table change

  if l_event_type = dbms_change_notification.EVENT_OBJCHANGE then

    -- loop each table

    for i in 1 .. l_numtables loop

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

      -- usually we call UTL_HTTP to send a notification

      -- to WEB Server, and here we only use some tables

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

      l_op_flag  := ntfnds.table_desc_array(i).opflags;

      l_tab_name := ntfnds.table_desc_array(i).table_name;

      -- get table name without schema name

      l_tab_name := substr(l_tab_name, instr(l_tab_name, '.') + 1);

    

      if bitand(l_op_flag, dbms_change_notification.ALL_ROWS) = 1 then

        -- no certain rowid

        insert into ntfn_msg

          (ora_trans, tab_name)

        values

          (l_ora_trans_id, l_tab_name);

      else

        -- loop each row in this table

        l_numrows := ntfnds.table_desc_array(i).numrows;

        for j in 1 .. l_numrows loop

          l_row_id := ntfnds.table_desc_array(i).row_desc_array(j).row_id;

          insert into ntfn_msg

            (ora_trans, tab_name, row_id)

          values

            (l_ora_trans_id, l_tab_name, l_row_id);

        end loop; -- end loop row

      end if;

    end loop; -- end loop table

  end if;

  commit;

end;

/

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

-- procession for registration and deregister

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

-- registration

declare

  l_reg_info sys.chnf$_reg_info;

  l_reg_id number;

  l_qosflags number;

  l_temp number;

begin

  l_qosflags := dbms_change_notification.QOS_ROWIDS;

  l_reg_info := sys.chnf$_reg_info('ntfs_callback',l_qosflags,0,0,0);

  l_reg_id := dbms_change_notification.NEW_REG_START(l_reg_info);

  -- register tables

  select count(1) into l_temp from ntfn_batch b, ntfn_trans t where b.id = t.original_rps_batch_id;  

  dbms_change_notification.REG_END;

end;

/

-- check register result

select r.regid,r.table_name from user_change_notification_regs r;

-- deregister

declare

  l_reg_id number := 23;

begin

  dbms_change_notification.DEREGISTER(l_reg_id);

end;

/  

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

-- do some change and check result after commit

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

update ntfn_batch b set b.rps_application_id=b.rps_application_id where rownum = 1;

commit;

update ntfn_batch b set b.rps_application_id=b.rps_application_id;

commit;

update ntfn_trans t set t.t2_identifier = t.t2_identifier;

commit;

select to_char(m.dt, 'yyyy-mm-dd hh24:mi:ss') dt,

       m.ora_trans,

       m.tab_name,

       m.row_id

  from ntfn_msg m

 order by 1, 3, 4;

 

SQL>

SQL> select to_char(m.dt, 'yyyy-mm-dd hh24:mi:ss') dt,

  2         m.ora_trans,

  3         m.tab_name,

  4         m.row_id

  5    from ntfn_msg m

  6   order by 1, 3, 4;

 

DT                  ORA_TRANS        TAB_NAME                       ROW_ID

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

2007-10-26 17:23:16 08004100D20D0100 NTFN_BATCH                     AAFCIbAAHAAAM5AAAA

2007-10-26 17:23:46 0A0061003ED50000 NTFN_BATCH                     AAFCIbAAHAAAM5AAAA

2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS                     AAFCIcAAHAAAM8IAAA

2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS                     AAFCIcAAHAAAM8IAAB

2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS                     AAFCIcAAHAAAM8IAAC

2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS                     AAFCIcAAHAAAM8IAAD

2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS                     AAFCIcAAHAAAM8IAAE

2007-10-26 17:24:26 0400200065D30000 NTFN_TRANS                     AAFCIcAAHAAAM8IAAF

 

8 rows selected

从 这个结果可以分析出

    执行了3个事务（简称1，2，3）

    事务1，2更改了NTFN_BATCH，事务3更改了NTFN_TRANS

 

Python

# -*- coding:utf-8 -*-

import cx_Oracle

import time

def callback(message):

    print("Message type:", message.type)

    print("Message database name:", message.dbname)

    print("Message tables:")

    for table in message.tables:

        print("--> Table Name:", table.name)

        print("--> Table Operation:", table.operation)

        if table.rows is not None:

            print("--> Table Rows:")

            for row in table.rows:

                print("--> --> Row RowId:", row.rowid)

                print("--> --> Row Operation:", row.operation)

                print("-" * 60)

        print("=" * 60)

connection = cx_Oracle.Connection("perf/perf@data211", events = True)

sub = connection.subscribe(callback = callback, timeout = 1800, rowids = True)

print("Subscription:", sub)

print("--> Connection:", sub.connection)

print("--> Callback:", sub.callback)

print("--> Namespace:", sub.namespace)

print("--> Protocol:", sub.protocol)

print("--> Timeout:", sub.timeout)

print("--> Operations:", sub.operations)

print("--> Rowids?:", sub.rowids)

# 可以注册多个表进行监控

sub.registerquery("select * from ntfn_batch")

sub.registerquery("select * from ntfn_trans")

while True:

    ## print("Waiting for notifications....")

    time.sleep(5)