Let us take an example
I got a complaint from a user “My application is hanging” please check.
I go to sql prompt as DBA then execute the command.
SQL> select username,sid,osuser,machine, terminal, program,event, blocking_session,wait_class from v$session where username is not null and wait_class <> 'Idle' and state ='WAITING' order by username
Output of this command is
USERNAME SID OSUSER MACHINE TERMINAL PROGRAM EVENT BLOCKING_SESSION WAIT_CLASS
TECHWAVE_ECOMMERCE 146 umesh HTSS\HTSS27 HTSS27 sqlplus.exe enq: TX - row lock contention 122 Application
Output clearly shows that user techwave_ecommerce having sid 146 from machine htss27 is waiting for the event row lock contention i.e. this is waiting to get a lock on a row for work. Session who has blocked this user is sid 122.
Now we will find the detail of blocking session having sid 122.
SQL> select sid,username,machine,program from v$session where sid=122
SID USERNAME MACHINE PROGRAM
122 TECHWAVE_ECOMMERCE htss27 SQL Developer
It means blocking session is from same system htss27 but using different program SQL developer.
Now find query which is hanging another user by command.
SQL> select sid, sql_text from v$session s, v$sql q where sid in (122) and ( q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)
122 update tek_store_master set store_url='asfda' where storeid=251
This is the query which hangs another user.
To solve the issue we have two options.
i) Commit session sid 122
ii) Kill session 122
Ask user to commit his session which is best solution. If it is not possible kill user’s session using commands.
SQL> alter system kill session ‘122,894’;
Your problem is solved now.