oracle 有没有办法监控 一个试图或者一个查询语句的结果发生变化,当查询结果发生变化的时候发出通知。
另外就是性能问题,我的这个表每年会由3亿条数据,而且新增操作非常频繁,每秒新增6条数据以上。
现在要做的是当出现符合条件的数据就向前台发通知,怎么样做效率会更高。
另外就是性能问题,我的这个表每年会由3亿条数据,而且新增操作非常频繁,每秒新增6条数据以上。
现在要做的是当出现符合条件的数据就向前台发通知,怎么样做效率会更高。
解决方案 »
- 求助该如何写这个期初至今累计的语句?
- oracle如何设置用户其默认表架构?
- Oracle存储过程报错,求助
- oracle11G 有对应的客户端吗?有的话给出下载网址!
- help!ora-01861 文字与格式字符串不匹配
- 求救!在线等待!数据库正常安装完报错?
- pl/sql中怎样判断一个字符串是否为字母或数字
- 索引无效!!我刚建的一个索引,最初还好用,怎么过了一会再查询又全表搜索了,晕!
- 刚刚接触unix下的oracle... 几个问题,
- 如何用pro c 写动态sql,给个例子.急!!!!
- ORA-01445: cannot select ROWID from, or sample, a join view without a key-preser
- EXP-00008: 遇到 ORACLE 错误 ORA-01455: 转换列溢出整数数据类型
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;
regid NUMBER;BEGIN
/* Register two queries for QRNC: */
/* 1. Construct registration information.
chnf_callback is name of notification handler.
QOS_QUERY specifies result-set-change notifications. */ reginfo := cq_notification$_reg_info (
'chnf_callback',
DBMS_CQ_NOTIFICATION.QOS_QUERY,
0, 0, 0
); /* 2. Create registration. */ regid := DBMS_CQ_NOTIFICATION.new_reg_start(reginfo); OPEN v_cursor FOR
SELECT dbms_cq_notification.CQ_NOTIFICATION_QUERYID, manager_id
FROM HR.EMPLOYEES
WHERE employee_id = 7902;
CLOSE v_cursor; OPEN v_cursor FOR
SELECT dbms_cq_notification.CQ_NOTIFICATION_QUERYID, department_id
FROM HR.departments
WHERE department_name = 'IT';
CLOSE v_cursor; DBMS_CQ_NOTIFICATION.reg_end;
END;
/
SELECT queryid, regid, TO_CHAR(querytext)
FROM user_cq_notification_queries;UPDATE DEPARTMENTS
SET DEPARTMENT_NAME = 'FINANCE'
WHERE department_name = 'IT';
SELECT * FROM nftablechanges;SELECT * FROM nfrowchanges;
具体看oracle官方文档