2023年8月3日发(作者：)
oracle监控sql语句对表的操作oracle 9i/10g关于执⾏sql语句的监控有的时候由于某种原因，我们需要监控对某个表的操作，例如：某个字段总是被莫名其妙的修改，此时就需要监控执⾏该操作的sql语句和操作⼈等信息，针对这种情况，9i和10g各有⾃⼰的监控⽅式。1、oracle 9i9i可以创建⼀个表，记录监控过程中感兴趣的东西Sql代码
1.
2.
3.
4.
5.
6.
7.
SQL> create table r_sql(
username varchar2(30),
client_ip varchar2(100),
sql_text varchar2(4000),
table_name varchar2(30),
owner varchar2(30)
);
然后创建⼀个触发器，来监控对监控对象的操作：
Java代码
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
CREATE OR REPLACE TRIGGER trigger_monitor_update_sql
BEFORE UPDATE ON _busi_handle_200903
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
dbms__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 LOOP
stmt := stmt || sql_text(i);
END LOOP;
dbms__line(stmt);
END IF;
insert into r_sql(USERNAME, CLIENT_IP, SQL_TEXT, TABLE_NAME, OWNER)
values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY');
end;
这样，执⾏对表_busi_handle_200903的update操作的⽤户名、ip地址、sql语句等信息就会保存在r_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 elementsare 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代码
1.
2.
3.
4.
5.
6.
7.
8.
9.
begin
dbms__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__policy(object_schema=>'zk', object_name => 'cm_busi_handle_200903',policy_name => 'cm_busi_audit');PL/SQL procedure successfully completed
2023年8月3日发(作者：)
oracle监控sql语句对表的操作oracle 9i/10g关于执⾏sql语句的监控有的时候由于某种原因，我们需要监控对某个表的操作，例如：某个字段总是被莫名其妙的修改，此时就需要监控执⾏该操作的sql语句和操作⼈等信息，针对这种情况，9i和10g各有⾃⼰的监控⽅式。1、oracle 9i9i可以创建⼀个表，记录监控过程中感兴趣的东西Sql代码
1.
2.
3.
4.
5.
6.
7.
SQL> create table r_sql(
username varchar2(30),
client_ip varchar2(100),
sql_text varchar2(4000),
table_name varchar2(30),
owner varchar2(30)
);
然后创建⼀个触发器，来监控对监控对象的操作：
Java代码
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
CREATE OR REPLACE TRIGGER trigger_monitor_update_sql
BEFORE UPDATE ON _busi_handle_200903
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
dbms__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 LOOP
stmt := stmt || sql_text(i);
END LOOP;
dbms__line(stmt);
END IF;
insert into r_sql(USERNAME, CLIENT_IP, SQL_TEXT, TABLE_NAME, OWNER)
values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY');
end;
这样，执⾏对表_busi_handle_200903的update操作的⽤户名、ip地址、sql语句等信息就会保存在r_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 elementsare 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代码
1.
2.
3.
4.
5.
6.
7.
8.
9.
begin
dbms__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__policy(object_schema=>'zk', object_name => 'cm_busi_handle_200903',policy_name => 'cm_busi_audit');PL/SQL procedure successfully completed
发布评论