Session Debugging

Some of the views used for session debugging are

If user is locking any objects the list of locked objects an be seen in the view v$locked_object using below query, form the below query we get the session_id, process_id , object_id, username.
SQL> select * from v$locked_object;

Using the session_id (SID) we can get the sql_id or prev_sql_id and transaction address using the view v$session using the below query.
SQL> select sid, saddr, paddr, taddr, username, sql_id, event, module from v$session where username is not null and sid=****;

If you did not get the present sql_id check the previous sql id
SQL> select sid, saddr, paddr, taddr, username, sql_id, prev_sql_id, event, module from v$session where username is not null and sid=****;

With the sql_id or prev_sql_id get the sql_text or sql_fulltext from view v$sql.
SQL> select sql_fulltext from v$sql where sql_id='*********';

To check the used undo record and used undo blocks by a transaction use v$transaction by the below query. (we can get the transaction address as taddr in v$session).
SQL> select used_urec, used_ublk from v$transaction where addr='transaction address';

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

To kill a process which is locking objects to get the process id (spid)

Get the username which is locking the objects from v$locked_object using below query.
SQL> select * from v$locked_object;

Join the v$session and v$process with the paddr in v$session and addr in v$process and sort the rows with the username which we got from the above.
SQL> select a.spid,b.username,b.module from v$process a,v$session b where a.addr=b.paddr and b.username='CHARAN';

check the process at OS level using the below command
$ ps -aef | grep <spid >
$ ps -aef | grep <process>

to kill the process use the below command
$ kill -9 <spid> --- user gets disconnected
$ kill -9 <process> --- the session will be killed

Oracle Basic