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';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.