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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2153413/