Tracing a User Session & File

Scenario will come where you want to track an user what they are doing in the database or it might be also a request from the user to track a job i.e what query is executed for performance issue and security check.
We are tracing a session or file to know about what are the activities that a particular user doing in the active database and you can read the extracted file for our future reference for sorting out the problem like of the error issue and some database related issues.

Steps to trace a user session:

1. The very first thing we need to extract the id of the user id and serial number (this is optional).

Use the below syntax to make most use of it:

Select sid, username, scehamname., machine,program from v$session where username is not null order by username.

It will give you all the username,schema name, machine name from which user is connected and  program by which the user is connected to server. Sql developer is the program for example.And from them you need only a specific ID.










For example: I have a user called 'HR' and I want to track the session of this user.

I have connected HR user in SQL DEVELOPER as shown in below figure.






Now as per the below query I have to find out the machine program and  most important the SID of this user so that i can track it's session.






From the above screen you can get to know that the SID = 153, Schema name=HR, Machine= ADMINB_IMH0T6H and Program = SQL developer of the user HR.

Here I have used HR as user and schema .But better you create your own user under a schema and then you will find out the username and schema name will be different. But in my case both are same.Because I have logged in as HR User and you know that HR is a schema name in Oracle Database.

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="give any name ex: tracingfile';




SQL> exec dbms_monitor.session_trace_enable(153);







Now the tracing file get enabled and now commands execute by user are stored in the execution pool and the trace file is stored in the user_dump_dest folder.

If you have completed of tracing the file.now the next step is to disable the tracing by executing the query.

sql> exec dbms_monitor.session_trace_disable(153);





Next Step is to find you trace file named as TraceExple by executing the below query.

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

Note: Trace File are stored in the user_dump_dest and the above query give you the path of the user_dump_dest.




Now from the above query you got your path of the user_dump_dest i.e




 And collect your trace filename from the above path and try to open it via tkprof command.


Thanks.


For more reference please go through the below link.

http://www.dbas-oracle.com/2009/06/tracing-user-session.html