Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Set CLIENT INFO column in v$session with DBMS_APPLICATION_INFO package

Set the value of Client info with DBMS_APPLICATION_INFO.SET_CLIENT_INFO. To see the other information from V$session. I trace the IPADDRESS of client by creating an logon trigger on it. So that i will get the IPaddress of the client from V$session view in CLIENT_INFO column.

Create a trigger which will put IPADDRESS value with help of DBMS_APPLICATION_INFO package:

CREATE OR REPLACE TRIGGER get_client_ip AFTER LOGON ON DATABASE
DECLARE
v_ip VARCHAR2(30);
BEGIN
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') INTO v_ip FROM DUAL;
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(v_ip);
END;
/

Connect the SQLPLUS from any client or server and check its ip is show in V$SESSION view.

SELECT CLIENT_INFO FROM V$SESSION WHERE USERNAME = 'HR';