Hey my guys and friends ,
I found a pretty weird situation on my this Oracle DB server (Version: 11.2.0.4.0 for single instance on Linux) - never showing IP on column "client_info" of view "V$SESSION" even if creating a TRIGGER particularly acquiring IP address.
The following is my trigger code being created on SYS shcema:
COLUMN text FORMAT a80
SELECT line, text FROM user_source WHERE name = 'ON_LOGON_TRIGGER';
LINE TEXT
---------- --------------------------------------------------------------------------------
1 TRIGGER on_logon_trigger
2 AFTER LOGON ON DATABASE
3 BEGIN
4 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv', 'ip_address'));
5 END;
SET LONG 9999
SET LINESIZE 200
SET PAGESIZE 60
SELECT trigger_body, status FROM user_triggers WHERE trigger_name = 'ON_LOGON_TRIGGER';
TRIGGER_BODY STATUS
-------------------------------------------------------------------------------- --------
BEGIN ENABLED
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('userenv', 'ip_address'));
END;
In the mean time, using the view "v$session" to check session numbers with column "client_info" and other important columns such as username, machine, and program. Nevertheless column "CLIENT_INFO" never shows the IP address, it's why?
SET LINESIZE 200
SET PAGESIZE 200
COLUMN username FORMAT a25
COLUMN machine FORMAT a20
COLUMN client_info FORMAT a15
COLUMN program FORMAT a16
COLUMN status FORMAT a8
SELECT username
, machine
, client_info
, program
, status
, count(*)
FROM v$session
WHERE username IS NOT NULL
-- AND status = 'ACTIVE'
AND program = 'JDBC Thin Client'
GROUP BY username
, machine
, client_info
, program
, status
ORDER BY count(*) DESC
, status
/
USERNAME MACHINE CLIENT_INFO PROGRAM STATUS COUNT(*)
------------------------- -------------------- --------------- ---------------- -------- ----------
TEST-00010 web10 JDBC Thin Client INACTIVE 20
TEST-00011 web11 JDBC Thin Client INACTIVE 16
TEST-00012 web12 JDBC Thin Client INACTIVE 10
TEST-00013 web13 JDBC Thin Client INACTIVE 10
TEST-00014 web14 JDBC Thin Client INACTIVE 10
TEST-00015 web15 JDBC Thin Client INACTIVE 10
TEST-00016 web16 JDBC Thin Client INACTIVE 10
TEST-00017 web17 JDBC Thin Client INACTIVE 10
TEST-00018 web18 JDBC Thin Client INACTIVE 10
9 rows selected.
Initially I thought that it might take an effection if rebooting oracle db servere but after which I tried, still no showing IP address. Is it a bug? (By the way my another oracle db server is okay).
Could you give me some good suggestion to troubleshoot it? Very appreciate if if any help. Thanks in advance!!!
Best Regards
Quanwen Zhao