20180427通過SQL_ID查出執行該SQL客戶端IP

[20180427]通過SQL_ID查出執行該SQL的客戶端IP.txt

--//論壇上鍊接http://www.itpub.net/thread-2101733-1-1.html的問題.自己測試看看.

1.環境:
SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> alter database add supplemental log data;
Database altered.

$ sqlplus -s  scott/book<<< "@@ &r/pt2 'select * from v\$database'" | grep -i supp
                   30 SUPPLEMENTAL_LOG_DATA_MIN      YES
                   31 SUPPLEMENTAL_LOG_DATA_PK       NO
                   32 SUPPLEMENTAL_LOG_DATA_UI       NO
                   40 SUPPLEMENTAL_LOG_DATA_FK       NO
                   41 SUPPLEMENTAL_LOG_DATA_ALL      NO
                   47 SUPPLEMENTAL_LOG_DATA_PL       NO

SCOTT@book> @ &r/spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        54        327 21565                    DEDICATED 21566       28        151 alter system kill session '54,327' immediate;

SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp;
  COUNT(*)
----------
105413504

--//sql_id=gau6fcukuvcz7

2.測試:

SCOTT@book> select session_id,session_serial#,sql_id,client_id,machine from V$ACTIVE_SESSION_HISTORY where sql_id='gau6fcukuvcz7' and IS_AWR_SAMPLE='Y';
SESSION_ID SESSION_SERIAL# SQL_ID        CLIENT_ID   MACHINE
---------- --------------- ------------- ----------- ---------
        54             327 gau6fcukuvcz7             xxxx4

--//首先說明一點,執行的sql語句一定要被awr抓取,否者無法發現.從sid,serial#看,確實能對上,但是僅僅知道機器名.

3.關閉附件日誌看看:
SCOTT@book> alter database drop supplemental log data;
Database altered.

SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp;
    COUNT(*)
------------
   105413504

SCOTT@book> column SAMPLE_TIME format a24
SCOTT@book> select SAMPLE_TIME, session_id,session_serial#,sql_id,client_id,machine from V$ACTIVE_SESSION_HISTORY where sql_id='gau6fcukuvcz7' and IS_AWR_SAMPLE='Y';
SAMPLE_TIME                SESSION_ID SESSION_SERIAL# SQL_ID        CLIENT_ID MACHINE
------------------------ ------------ --------------- ------------- --------- -------
2018-04-27 09:05:47.837            54             327 gau6fcukuvcz7           xxxx4
2018-04-27 08:50:26.653            54             327 gau6fcukuvcz7           xxxx4

--//不管開啟附加日誌與否,僅僅知道機器名.可以通過變通的方法給client_id加入ip地址,這樣就可以通過sql_id知道是那個IP執行的.

4.通過觸發器給client_id賦值IP 地址:

--//我以前寫的指令碼,僅僅取出關鍵部分:
CREATE OR REPLACE TRIGGER SYS.ENABLE_IP_address
   AFTER LOGON
   ON DATABASE
DECLARE
   v_exe           v$session.program%TYPE;
   v_client_info   v$session.client_info%TYPE;
   v_sid           v$session.SID%TYPE;
   v_osuser        v$session.osuser%TYPE;
   v_machine       v$session.machine%TYPE;
   v_module        v$session.module%TYPE;
   v_ok            VARCHAR (10);
BEGIN
   SELECT NVL (SYS_CONTEXT ('userenv', 'ip_address'), '127.0.0.1')
     INTO v_client_info
     FROM DUAL;

   -- add and set clint_ip to application_info
   DBMS_APPLICATION_INFO.set_client_info (v_client_info);
   --增加的程式碼
   DBMS_SESSION.set_identifier (v_client_info);
END;
/

5.退出繼續測試:
SCOTT@78> @ &r/spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        80         19 6220:3344                DEDICATED 21800       30         10 alter system kill session '80,19' immediate;

SCOTT@book> select count(*) from emp,emp,emp,emp,emp,emp,emp;
    COUNT(*)
------------
   105413504

SCOTT@book> select SAMPLE_TIME, session_id,session_serial#,sql_id,client_id,machine from V$ACTIVE_SESSION_HISTORY where sql_id='gau6fcukuvcz7' and IS_AWR_SAMPLE='Y' order by SAMPLE_TIME desc;
SAMPLE_TIME              SESSION_ID SESSION_SERIAL# SQL_ID        CLIENT_ID    MACHINE
------------------------ ---------- --------------- ------------- ------------ --------------------
2018-04-27 09:21:09.006          80              19 gau6fcukuvcz7 192.168.xx.x WORKGROUP\YYYY
2018-04-27 09:05:47.837          54             327 gau6fcukuvcz7              xxxx4
2018-04-27 08:50:26.653          54             327 gau6fcukuvcz7              xxxx4

--//這樣client_id就記錄IP資訊.通過增加DBMS_SESSION.set_identifier (v_client_info);就可以在client_id記錄IP.
--//不過我也遇到一個問題,我不知道為什麼我們應用這樣做產生大量的跟蹤檔案,我最終取消這個設定.

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