oracle operation_type,oracle 怎么 监控数据变化

oracle 怎么 监控数据变化

热度：1105   发布时间：2016-04-24 08:35:43

oracle 如何 监控数据变化

oracle 有没有办法监控 一个试图或者一个查询语句的结果发生变化，当查询结果发生变化的时候发出通知。

另外就是性能问题，我的这个表每年会由3亿条数据，而且新增操作非常频繁，每秒新增6条数据以上。

现在要做的是当出现符合条件的数据就向前台发通知，怎么样做效率会更高。

------解决方案--------------------

1.Connect to the database AS SYSDBA.

2.Grant the required privileges to HR:

GRANT EXECUTE ON DBMS_CQ_NOTIFICATION TO HR;

GRANT CHANGE NOTIFICATION TO HR;

3.Enable the JOB_QUEUE_PROCESSES parameter to receive notifications:

ALTER SYSTEM SET "JOB_QUEUE_PROCESSES"=4;

4.Connect to the database as a non-SYS user (such as HR).

5.Create database tables to hold records of notification events received:

-- Create table to record notification events.

DROP TABLE nfevents;

CREATE TABLE nfevents (

regid NUMBER,

event_type NUMBER

);

-- Create table to record notification queries:

DROP TABLE nfqueries;

CREATE TABLE nfqueries (

qid NUMBER,

qop NUMBER

);

-- Create table to record changes to registered tables:

DROP TABLE nftablechanges;

CREATE TABLE nftablechanges (

qid NUMBER,

table_name VARCHAR2(100),

table_operation NUMBER

);

-- Create table to record ROWIDs of changed rows:

DROP TABLE nfrowchanges;

CREATE TABLE nfrowchanges (

qid NUMBER,

table_name VARCHAR2(100),

row_id VARCHAR2(2000)

);

CREATE OR REPLACE PROCEDURE chnf_callback (

ntfnds IN CQ_NOTIFICATION$_DESCRIPTOR

)

IS

regid NUMBER;

tbname VARCHAR2(60);

event_type NUMBER;

numtables NUMBER;

operation_type NUMBER;

numrows NUMBER;

row_id VARCHAR2(2000);

numqueries NUMBER;

qid NUMBER;

qop NUMBER;

BEGIN

regid := ntfnds.registration_id;

event_type := ntfnds.event_type;

INSERT INTO nfevents (regid, event_type)

VALUES (chnf_callback.regid, chnf_callback.event_type);

numqueries :=0;

IF (event_type = DBMS_CQ_NOTIFICATION.EVENT_QUERYCHANGE) THEN

numqueries := ntfnds.query_desc_array.count;

FOR i IN 1..numqueries LOOP -- loop over queries

qid := ntfnds.query_desc_array(i).queryid;

qop := ntfnds.query_desc_array(i).queryop;

INSERT INTO nfqueries (qid, qop)

VALUES(chnf_callback.qid, chnf_callback.qop);

numtables := 0;

numtables := ntfnds.query_desc_array(i).table_desc_array.count;

FOR j IN 1..numtables LOOP -- loop over tables

tbname :=

ntfnds.query_desc_array(i).table_desc_array(j).table_name;

operation_type :=

ntfnds.query_desc_array(i).table_desc_array(j).Opflags;

INSERT INTO nftablechanges (qid, table_name, table_operation)

VALUES (

chnf_callback.qid,

tbname,

operation_type

);

IF (bitand(operation_type, DBMS_CQ_NOTIFICATION.ALL_ROWS) = 0) THEN

numrows := ntfnds.query_desc_array(i).table_desc_array(j).numrows;

ELSE

numrows :=0; -- ROWID info not available

END IF;

-- Body of loop does not run when numrows is zero.

FOR k IN 1..numrows LOOP -- loop over rows

Row_id :=

ntfnds.query_desc_array(i).table_desc_array(j).row_desc_array(k).row_id;

INSERT INTO nfrowchanges (qid, table_name, row_id)

VALUES (chnf_callback.qid, tbname, chnf_callback.Row_id);

END LOOP; -- loop over rows

END LOOP; -- loop over tables

END LOOP; -- loop over queries

END IF;

COMMIT;

END;

/

DECLARE

reginfo CQ_NOTIFICATION$_REG_INFO;

mgr_id NUMBER;

dept_id NUMBER;

v_cursor SYS_REFCURSOR;