Sunday, November 16, 2008

Blocking session issue how to resolve them

***************** blocking session checking & blocking sql ************
select count(*) from v$Lock where block=1;
col host_name for a20
select instance_name, status , host_name from v$instance;
select count(*) from v$Lock where block=1;
select prev_hash_value from v$session where sid='&466';

select sql_text from v$sqlarea where hash_value='&hash_value';
************************* blocking session picture **************************
SQL> select sid from v$lock where block=1;
O/p give u the sid which is blocking
SQL> select prev_hash_value from v$session where sid='&466';
O/P paste sid it gives the hash_value
SQL> select sql_text from v$sqlarea where hash_value='&hash_value'
paste ths hash_value to get the sql blocking the session
*************************************************************************

2 comments:

Mohammed Aijaz Ur Rahman Quraishi said...

Awesome Explaination Bro... Keep up the good work!!

eSeal said...

Gud Explanation and more informative.

Thanks!