Database Change Notification

最新推荐文章于 2022-11-01 09:22:59 发布

Honcho

最新推荐文章于 2022-11-01 09:22:59 发布

阅读量2.5k

点赞数

分类专栏： Administration 文章标签： database notifications table insert delete date

本文链接：https://blog.csdn.net/sweethouse1128/article/details/6793425

版权

Administration 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

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.

Once use this feature for change notification of column level, and found that once the amount of updated records is more than a small value during a transaction, such as 200, then the change notification considers the corresponding tables are fully updated and it consumes much database resources, such as CPU time, I/O throughput, etc, so just as Oracle official document said, For best performance of change notification, Registered objects are few and mostly read-only and that modifications to those objects are the exception rather than the rule. If the object is extremely volatile, then it cause a large number of invalidation notifications to be sent, and potentially a lot of storage in the invalidation queue on the server. If there are frequent and a large number of notifications, it can slow down OLTP throughput due to the overhead of generating the notifications.

The following is the example of how to use database change notification:
1.configuration job_queue_processes and create test user
SQL> alter system set job_queue_processes=1;

System altered.

SQL> create user test identified by test default tablespace users temporary tablespace temp quota unlimited on users;

User created.

SQL> grant connect,create table,create procedure,create sequence to test;

Grant succeeded.

SQL> grant change notification to test;

Grant succeeded.

SQL> grant execute on dbms_change_notification to test;

Grant succeeded.

2.Create test table
SQL> conn test/test
Connected.

SQL> create table t1 (id number,description varchar2(50),constraint t1_pk primary key (id));

Table created.

SQL> insert into t1 values (1,'One');

1 row created.

SQL> insert into t1 values (2,'Two');

1 row created.

SQL> commit;

Commit complete.

SQL> create table t2 ( id number,t1_id number,description varchar2(50),constraint t2_pk primary key (id),
2 constraint t2_t1_fk foreign key (t1_id) references t1 (id));

Table created.

SQL> insert into t2 values (1,1,'One-One');

1 row created.

SQL> insert into t2 values (2,1,'Two-One');

1 row created.

SQL> insert into t2 values (3,2,'Three-Two');

1 row created.

SQL> insert into t2 values (4,2,'Four-Two');

1 row created.

SQL> commit;

Commit complete.

3.Create record table
SQL> create table notifications (id number,message varchar2(4000),notification_date date);

Table created.

SQL> create sequence notifications_seq;

Sequence created.

4.Create notice processing program
CREATE OR REPLACE PROCEDURE tables_changed_chnt(ntfnds IN SYS.chnf$_desc) IS
l_regid           NUMBER;
l_table_name      VARCHAR2(60);
l_event_type      NUMBER;
l_numtables       NUMBER;
l_operation_type NUMBER;
l_numrows         NUMBER;
l_row_id          VARCHAR2(20);
l_operation       VARCHAR2(20);
l_message         VARCHAR2(4000) := NULL;
BEGIN
l_regid      := ntfnds.registration_id;
l_numtables := ntfnds.numtables;
l_event_type := ntfnds.event_type;
IF l_event_type = DBMS_CHANGE_NOTIFICATION.EVENT_OBJCHANGE THEN
    FOR i IN 1 .. l_numtables LOOP
      l_table_name      := ntfnds.table_desc_array(i).table_name;
      l_operation_type := ntfnds.table_desc_array(i).Opflags;
      l_row_id:= ntfnds.table_desc_array(i).row_desc_array(1).row_id;
      IF (BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.ALL_ROWS) = 0) THEN
        l_numrows := ntfnds.table_desc_array(i).numrows;
      ELSE
        l_numrows := 0; /* ROWID INFO NOT AVAILABLE */
      END IF;
      CASE
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.INSERTOP) != 0 THEN
          l_operation := 'Records Inserted';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.UPDATEOP) != 0 THEN
          l_operation := 'Records Updated';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.DELETEOP) != 0 THEN
          l_operation := 'Records Deleted';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.ALTEROP) != 0 THEN
          l_operation := 'Table Altered';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.DROPOP) != 0 THEN
          l_operation := 'Table Dropped';
        WHEN BITAND(l_operation_type, DBMS_CHANGE_NOTIFICATION.UNKNOWNOP) != 0 THEN
          l_operation := 'Unknown Operation';
        ELSE
          l_operation := '?';
      END CASE;
      l_message := 'Table (' || l_table_name || ') - ' || l_operation || '. Rows=' || l_numrows||' rowid='||l_row_id;
      INSERT INTO notifications (id, message, notification_date)
      VALUES (notifications_seq.NEXTVAL, l_message, SYSDATE);
      COMMIT;
    END LOOP;
END IF;
END;
/

DECLARE
l_regds     SYS.CHNF$_REG_INFO;
l_regid     NUMBER;
l_qosflags NUMBER;
l_id        t1.id%TYPE;
BEGIN
l_qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE +
                DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
l_regds := SYS.CHNF$_REG_INFO ('tables_changed_chnt', l_qosflags, 0,0,0);
l_regid := DBMS_CHANGE_NOTIFICATION.new_reg_start (l_regds);
SELECT id into l_id FROM   t1 WHERE ROWNUM = 1;
SELECT id into l_id FROM   t2 WHERE ROWNUM = 1;
DBMS_CHANGE_NOTIFICATION.reg_end;
END;
/

PL/SQL procedure successfully completed.

5.Test
SQL> insert into t1 values (4,'Four');

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from notifications;

SQL> select * from t2;

        ID      T1_ID DESCRIPTION
---------- ---------- --------------------------------------------------
         1          1 One-One
         2          1 Two-One
         3          2 Three-Two
         4          2 Four-Two

SQL> insert into t2 values (5,4,'Five-f');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from notifications;

SQL> update t2 set description='Five-Four' where id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from notifications;

6 rows selected.

SQL> delete t2 where id=5;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from notifications;

6 rows selected.

---------- Don't konw why there is no record delete opration.

SQL> delete t1 where id=4;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from notifications;

        ID MESSAGE                                                                NOTIFICATION_DATE
---------- ---------------------------------------------------------------------- -------------------
         1 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD    2011-09-20 11:27:49
         2 Table (TEST.T1) - Records Inserted. Rows=1 rowid=AAANBfAAEAAAAGEAAD    2011-09-20 11:27:49
         3 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE    2011-09-20 11:30:59
         4 Table (TEST.T2) - Records Inserted. Rows=1 rowid=AAANBhAAEAAAAGUAAE    2011-09-20 11:30:59
         5 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE     2011-09-20 11:31:54
         6 Table (TEST.T2) - Records Updated. Rows=1 rowid=AAANBhAAEAAAAGUAAE     2011-09-20 11:31:54
         7 Table (TEST.T2) - Records Deleted. Rows=1 rowid=AAANBhAAEAAAAGUAAE     2011-09-20 11:32:29
         8 Table (TEST.T2) - Records Deleted. Rows=1 rowid=AAANBhAAEAAAAGUAAE     2011-09-20 11:32:29
         9 Table (TEST.T1) - Records Deleted. Rows=1 rowid=AAANBfAAEAAAAGEAAD     2011-09-20 11:33:40
        10 Table (TEST.T1) - Records Deleted. Rows=1 rowid=AAANBfAAEAAAAGEAAD     2011-09-20 11:33:40

10 rows selected.

-------- Why here have the delete opration.

Honcho

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Database Change Notification

Database Change Notification is a feature that enables client applications to register queries with the database and receive notifications i
复制链接

扫一扫