日记 · 日常札记

oracle 9i/10g关于执行sql语句的监控

有的时候由于某种原因，我们需要监控对某个表的操作，例如：某个字段总是被莫名其妙的修改，此时就需要监控执行该操作的sql语句和操作人等信息，针对这种情况，9i和10g各有自己的监控方式。

1、oracle 9i9i可以创建一个表，记录监控过程中感兴趣的东西

Sql代码 SQL> create table zk.monitor_sql( username varchar2(30), client_ip varchar2(100), sql_text varchar2(4000), table_name varchar2(30), owner varchar2(30) ); SQL> create table zk.monitor_sql(

username varchar2(30),

client_ip varchar2(100),

sql_text varchar2(4000),

table_name varchar2(30),

owner varchar2(30)

);

然后创建一个触发器，来监控对监控对象的操作：

Java代码 CREATE OR REPLACE TRIGGER trigger_monitor_update_sql BEFORE UPDATE ON zk.cm_busi_handle_200903 declare n number; stmt varchar2(4000); sql_text ora_name_list_t; begin dbms_output.put_line(ora_sql_txt(sql_text)); n := ora_sql_txt(sql_text); IF nvl(n,200)=200 THEN raise_application_error(-20001,'ora_sql_txt未捕捉到任何语句.sql_txt未初始化'); ELSE FOR i IN 1..n LOOP stmt := stmt || sql_text(i); END LOOP; dbms_output.put_line(stmt); END IF; insert into zk.monitor_sql(USERNAME, CLIENT_IP, SQL_TEXT, TABLE_NAME, OWNER) values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY'); end; CREATE OR REPLACE TRIGGER trigger_monitor_update_sql

BEFORE UPDATE ON zk.cm_busi_handle_200903

declare

n number;

stmt varchar2(4000);

sql_text ora_name_list_t;

begin

dbms_output.put_line(ora_sql_txt(sql_text));

n := ora_sql_txt(sql_text);

IF nvl(n,200)=200 THEN

raise_application_error(-20001,'ora_sql_txt未捕捉到任何语句.sql_txt未初始化');

ELSE FOR i IN 1..n LOOP

stmt := stmt || sql_text(i);

END LOOP;

dbms_output.put_line(stmt);

END IF;

insert into zk.monitor_sql(USERNAME, CLIENT_IP, SQL_TEXT, TABLE_NAME, OWNER)

values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY');

end;

这样，执行对表zk.cm_busi_handle_200903的update操作的用户名、ip地址、sql语句等信息就会保存在zk.monitor_sql表里了。其中关键的一步是：ora_sql_txt(sql_text)

原型：ora_sql_txt(sql_text out ora_name_list_t)官方解释：Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken into multiple PL/SQL table elements. The function return value shows the number of elements are in the PL/SQL table

但是ora_sql_txt(sql_text)只有在9i版本允许在dml触发器使用，在10g后该函数总是返回空，据说只能在ddl触发器使用。针对这一点的说法是：On database versions 9.2.0.1 to 9.2.0.6 ora_sql_text works and returns the calling text for dml triggers, where as starting from 9.2.0.7 the behavior has changed and returns NULL.

2、oracle 10g

说是10g，9i应该也可以。其实我们可以结合v$session和v$sqltext来查看对某个表的操作，也可以采取如下的方式，创建一个策略：

Sql代码 begin dbms_fga.add_policy(object_schema => 'zk', --schema名(默认当前操作用户) object_name => 'cm_busi_handle_200903', --被操作object对象 policy_name => 'cm_busi_audit', --policy名(唯一) audit_condition => 'process_id = 1', --条件 audit_column => 'oper_date', --列，如果有两个以上用“，”分隔 statement_types => 'update', --受影响的操作，如果有两个以上用“，”分隔 enable=>TRUE ); end; begin

dbms_fga.add_policy(object_schema => 'zk', --schema名(默认当前操作用户)

object_name => 'cm_busi_handle_200903', --被操作object对象

policy_name => 'cm_busi_audit', --policy名(唯一)

audit_condition => 'process_id = 1', --条件

audit_column => 'oper_date', --列，如果有两个以上用“，”分隔

statement_types => 'update', --受影响的操作，如果有两个以上用“，”分隔

enable=>TRUE );

end;

查看捕获策略select * from dba_audit_policies;

查看捕获信息select timestamp,userhost,os_user,db_user,object_schema,object_name,statement_type,sql_text,policy_name from dba_fga_audit_trail order by timestamp;

策略的删除：

exec dbms_fga.drop_policy(object_schema=>'zk', object_name => 'cm_busi_handle_200903',policy_name => 'cm_busi_audit');PL/SQL procedure successfully completed

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/Phoenix_99/archive/2011/03/04/6223612.aspx

原文章浏览量：5610

原文章分类：数据库

原文链接：https://www.iteye.com/blog/lizhiyu-982770

oracle监控sql语句对表的操作