[20130226]跟蹤特定IP的sql語句.txt

lfree發表於2013-02-27
SQL
[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語句.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-754842/，如需轉載，請註明出處，否則將追究法律責任。

相關文章