[20130226]跟踪特定IP的sql语句.txt

[20130226]跟踪特定IP的sql语句.txt

工作需要,跟踪特定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语句.