Tracing a User Session

Sometimes we want to know what a user is doing i.e. what query is executed for performance related issues or security check. The easiest way to do is tracing the user session. Tracking stores user sessions all data in a file which can be seen any time.
Steps to trace a user session.
1. First step is to identify the ID of the user’s session which we want to trace. This is known as SID.
Use command by login as DBA.

SQL> select sid, username, schemaname, machine, program from v$session where username is not null order by username;

SID UserName SchemaName Machine Program

148 TECHWAVE_ECOMMERCE TECHWAVE_ECOMMERCE htss12 SQL Developer
131 TECHWAVE_ECOMMERCE TECHWAVE_ECOMMERCE htss51 SQL Developer
141 TECHWAVE_ECOMMERCE TECHWAVE_ECOMMERCE htss51 SQL Developer
140 TECHWAVE_ECOMMERCE TECHWAVE_ECOMMERCE htss51 SQL Developer

Which shows user_name, Schema_name, Machine form which user is connected and program by which the user is connected to server. Sql developer is the program in my example.

My target user is htss12 then the SID for it is 148. Now it is clear I will trace SID 148.

2. Second step is to enable tracing for this user SID. Before enable tracing it’s good to give name to your trace file otherwise it will difficult to identify trace file generated. Use command to give unique name to your trace file from sql prompt.
SQL> alter session set tracefile_identifier="TraceExple";
Session altered.
Now enable tracing for user by
SQL> exec dbms_monitor.session_trace_enable(148);
PL/SQL procedure successfully completed.
The tracing is enabled. Now commands execute by user will be stored with execution plan in named “TraceExple” file.
3. If you have done with your tracing now you have to disable tracing to read the output of trace file. Using command
SQL> exec dbms_monitor.session_trace_disable(148);

PL/SQL procedure successfully completed.

Next step is to find and read trace file.

4. Trace file are stored into user_dump_destination file by default. Use command to find path of your user_dump_dest.

SQL> select name,value from v$parameter where name like 'user_dump_dest';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
user_dump_dest
/home/oracle/oracle/product/10.2.0/db_2/admin/orcl/udump

It means your trace file is stored in /home/oracle/oracle/product/10.2.0/db_2/admin/orcl/udump by name TraceExple.

5. Next step is to make this trace file readable to do it we will use tkprof utility. But remember this command will execute outside the sql prompt.

Syntax:- tkprof
[oracle@htss06 bin]$ ./tkprof /home/oracle/oracle/product/10.2.0/db_2/admin/orcl/udump/orcl_ora_10350_TraceExple.trc /home/oracle/Desktop/trc_exp.prf
TKPROF: Release 10.2.0.1.0 - Production on Mon Jun 29 18:46:03 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Now the file create at the destination is in readable form. Unfortunately my trace file is empty. Now you can read your trace file.

Thanks
Umesh Sharma

No comments:

Post a Comment