[20130226]跟踪特定IP的sql语句.txt
工作需要,跟踪特定IP地址发出的sql语句.可惜我们生产系统是10g的,如果是11G支持trcsess可以合并trc文件,再分析.
自己做一个测试看看.
http://space.itpub.net/267265/viewspace-754003
1.建立logon触发器:
2.以scott,system用户为例子来说明:
SQL> show parameter iden
--bbb.txt记录的sql语句.
工作需要,跟踪特定IP地址发出的sql语句.可惜我们生产系统是10g的,如果是11G支持trcsess可以合并trc文件,再分析.
自己做一个测试看看.
http://space.itpub.net/267265/viewspace-754003
1.建立logon触发器:
CREATE OR REPLACE TRIGGER SYS.on_logon_trigger
AFTER LOGON ON DATABASE
DECLARE
v_client_info v$session.client_info%TYPE;
v_sid v$session.SID%TYPE;
BEGIN
select SYS_CONTEXT ('userenv', 'ip_address') into v_client_info from dual ;
DBMS_APPLICATION_INFO.set_client_info (v_client_info);
--增加的代码
dbms_session.set_identifier(v_client_info);
EXECUTE IMMEDIATE 'alter session set tracefile_identifier = ''' || replace(nvl(v_client_info,'local'),'.','_')||'''';
END;
/
2.以scott,system用户为例子来说明:
SQL> show parameter iden
NAME TYPE VALUE
------------------------------------ -------------------------- ---------------------
tracefile_identifier string 172_16_100_6
--在回话2执行如下:
exec dbms_monitor.client_id_trace_enable(client_id=>'172.16.100.6', waits=>true, binds=>true);
以scott用户登录执行如下:
select count(*) from dept;
select * from emp where empno=7934;
以system用户登录执行如下:
select count(*) from dba_objects ;
--在回话2执行如下:
exec dbms_monitor.client_id_trace_disable(client_id=>'172.16.100.6');
3. 检查文件:
$ cd /u01/app/oracle11g/diag/rdbms/test/test/trace
$ ls -l *172_16_100_6*.trc
-rw-r----- 1 oracle11g oinstall 27632 2013-02-27 12:00:19 test_ora_16793_172_16_100_6.trc
-rw-r----- 1 oracle11g oinstall 18808 2013-02-27 12:00:30 test_ora_16855_172_16_100_6.trc
--合并文件.
$ trcsess utput=172_16_100_6.out clientid=172.16.100.6 *172_16_100_6.trc
$ tkprof trc1.out aaa.txt record=bbb.txt
$ cat bbb.txt
SELECT USER FROM DUAL ;
BEGIN DBMS_OUTPUT.DISABLE; END;
/
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND (UPPER(USER) LIKE USERID) ;
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('SQL*Plus') LIKE UPPER(PRODUCT)) AND ((UPPER(USER) LIKE USERID) OR (USERID = 'PUBLIC')) AND (UPPER(ATTRIBUTE) = 'ROLES') ;
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
/
SELECT DECODE('A','A','1','2') FROM DUAL ;
BEGIN DBMS_OUTPUT.ENABLE(1000000); END;
/
BEGIN DBMS_OUTPUT.DISABLE; END;
/
select count(*) from dept ;
select * from emp where empno=7934 ;
SELECT USER FROM DUAL ;
BEGIN DBMS_OUTPUT.DISABLE; END;
/
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
/
SELECT DECODE('A','A','1','2') FROM DUAL ;
BEGIN DBMS_OUTPUT.ENABLE(1000000); END;
/
BEGIN DBMS_OUTPUT.DISABLE; END;
/
select count(*) from dba_objects ;
--bbb.txt记录的sql语句.
