How does one trace a session based on user

CREATE OR REPLACE TRIGGER sys.session_trace_on
— to be created by sys user
AFTER LOGON ON database
DECLARE
machinename VARCHAR2(64);
osuserid VARCHAR2(30);
ora_username VARCHAR2(30) DEFAULT NULL;
os_username VARCHAR2(30);
v_sid NUMBER;
v_serial NUMBER;
v_program VARCHAR2(48);
v_numuser NUMBER;
CURSOR c1 IS
SELECT sid, serial#, osuser, machine, program
FROM v$session
WHERE audsid = userenv(‘sessionid’)
— and upper(machine) like ‘ABC123’
and username = ‘EPCBATCH’;
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, osuserid, machinename,v_program;
IF c1%FOUND THEN
DBMS_SESSION.set_sql_trace (TRUE);
END IF;
CLOSE c1;
END;
/

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s