Oracle Blocking Sessions and Lock Scripts -2

Hi,

While you are using your program which is connected to Oracle database, you can feel slowness on application related with Oracle database. This problem is probably related with blocking session and database lock.

You can find momentarily blocking session and lock with below scripts.

select s1.username || '@' || s1.machine
|| ' ( THIS SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

 

You can generate Kill script of Blocking sessions like below.

SELECT 
'alter system kill session ''' || SID || ',' || s.serial# || ',@'||inst_id||''';',sid,username,serial#,process,NVL (sql_id, 0),
sql_address,blocking_session,wait_class,event,p1,p2,p3,seconds_in_wait
FROM gv$session s WHERE blocking_session_status = 'VALID'
OR sid IN (SELECT blocking_session
FROM gv$session WHERE blocking_session_status = 'VALID');

 

query result will be like below if you have blocking session in your database.

alter system kill session '1,39390,@1';
alter system kill session '2,2536,@1';
alter system kill session '3,26324,@1';

 

then run above script in new session and kill these blocking sessions to solve problem.

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

One thought on “Oracle Blocking Sessions and Lock Scripts -2

  • April 12, 2019 at 9:37 am
    Permalink

    Can I just say what a comfort to uncover somebody that actually understands what they are discussing online. You certainly know how to bring an issue to light and make it important. More and more people have to check this out and understand this side of your story. It’s surprising you are not more popular because you definitely have the gift.

    Reply