[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.
--//不过我也遇到一个问题,我不知道为什么我们应用这样做产生大量的跟踪文件,我最终取消这个设置.
