Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Saturday, 7 July 2012

Identify specific user using "Trace file identifier"

Using “tracefile_identifier” Oracle will add that to the name of the user’s trace filename so the file can easily be identified. For example if one needs to trace multiple users session who have different schema or OS user then using “tracefile_identifier” one can identify the trace file specific to a user.
Example: The following trigger will create a trace file with the user’s OSuser and machine part of the filename for any user who login with “HRMS”. You must login with Sysdba to create this trigger.

CREATE OR REPLACE TRIGGER sys.session_trace_on AFTER LOGON ON database
DECLARE
v_machinename VARCHAR2(64);
v_ora_username VARCHAR2(30) DEFAULT NULL;
v_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 sid = userenv('sid') and username = 'HRMS';
BEGIN
OPEN c1;
FETCH c1 INTO v_sid, v_serial, v_os_username, v_machinename, v_program;
IF c1%FOUND THEN
-- DBMS_SESSION.set_sql_trace (TRUE);
v_machinename := replace(replace(v_machinename, '\', '_'), '/', '_');
v_os_username := replace(replace(v_os_username, '\', '_'), '/', '_');
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''' || trim(v_os_username) || '''';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
CLOSE c1;
END;
/
Alternatively you can write this trigger as:
CREATE OR REPLACE TRIGGER hrms_login
AFTER LOGON ON hrms_login.SCHEMA
BEGIN
DBMS_SESSION.SET_SQL_TRACE(TRUE);
END;
/
Enabling/Disabling this Trigger:
ALTER TRIGGER hrms_login ENABLE;
ALTER TRIGGER hrms_login DIABLE;
You can also enable instance level by adding it into the init.ora paramter: sql_trace=TRUE
select * from dba_triggers
where trigger_name='SESSION_TRACE_ON';
Drop trigger SESSION_TRACE_ON;

Enabling SQL Trace (Session Level):
ALTER SESSION SET sql_trace=TRUE;
Disabling SQL Trace:
ALTER SESSION SET sql_trace=FALSE;
Note: You Must GRANT EXECUTE ON DBMS_SYSTEM TO <hrms>; --if there is no DBMS_SYSTEM grant available.

0 comments:

Post a Comment