Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Paste command syntax Linux/Unix
  • Very clear article about oracle dataguard Oracle
  • move_arch_files.ksh Linux/Unix
  • Oracle 10g for solaris 10 Oracle
  • V$CONTROLFILE_RECORD_SECTION reference notes. Oracle
  • How to stop OCSSD Daemon Oracle
  • ipcs -l Linux/Unix
  • sid_wise_sql.sql Oracle
  • Roles and Stored Object behaviour Oracle
  • Generate SSH without password authentication. Linux/Unix
  • mutex in Oracle 10.2.0.2 or Oracle 10g Oracle
  • pvm_rbs1.sql (to collect rbs info from db) Oracle
  • Mutating Table Error while using database trigger Oracle
  • Processes Parameter decision Oracle
  • shr1.sql for MTS or Shared server configuration Oracle
pvmehta.com SQL scripts

Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle

Posted on 11-Jun-202311-Jun-2023 By Admin No Comments on Find which sessions is accessing object that prevent your session to have exclusive locks in Oracle

If you want exclusive lock on any table, you need to make sure that no other session is  accessing it. This happens when you want to run DDL or any ALTER statements, that internally needs exclusive lock. Following script will to find the blocker session and provide killing statement for clearing locks. 

col owner format a20
set lines 120 pages 200

accept v_owner prompt "Enter owner:"
accept v_objnm prompt "Enter object_name:"

select a.inst_id, substr(d.sid,1,7) SID,substr(d.serial#,1,7) SERIAL,
substr(to_char(d.logon_time,'hh24:mi:ss'),1,8) time
,substr(b.object_name,1,23) OBJECT_NAME,
b.owner, d.machine, d.program, d.module,
'alter system kill session ''' || d.sid || ',' || d.serial# || ', @' || d.inst_id || ''';' || chr(10) || lpad('+', 80, '+')
from gv$lock a,dba_objects b,gv$session d
where (a.id1=b.object_id or a.id2 = b.object_id)
and a.inst_id = d.inst_id
/*and object_name = 'MAXVALUE'*/
and d.sid=a.sid
--and b.owner not in ('SYS')
and b.owner = upper('&v_owner')
and b.object_name = upper('&v_objnm')
order by d.username
/


select
'alter system kill session ''' || d.sid || ',' || d.serial# || ', @' || d.inst_id || ''';' || chr(10)
from gv$lock a,dba_objects b,gv$session d
where (a.id1=b.object_id or a.id2 = b.object_id)
and a.inst_id = d.inst_id
/*and object_name = 'MAXVALUE'*/
and d.sid=a.sid
--and b.owner not in ('SYS')
and b.owner = upper('&v_owner')
and b.object_name = upper('&v_objnm')
order by d.username
/
Oracle, SQL scripts

Post navigation

Previous Post: How to collect CPU usage on Linux using Shell script
Next Post: Oracle GoldenGate lag monitoring shell script

Related Posts

  • sqlnet.ora paramters Oracle
  • cp_filesystem.sql Oracle
  • Oracle Data Direct to TAPE Oracle
  • Find Multiple levels of object dependencies : depen.sql Oracle
  • Histogram Overview Oracle
  • findinfo.sql (SQL for getting CPU and Active session info) Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Find All internal Parameters Oracle
  • Renaming Global Name GLOBAL_NAME Oracle
  • create trigger syntax Oracle
  • logminer and my_lbu Oracle
  • Query to Generate aggregate on every 30 mins. Oracle
  • find_pk.sql /* Find Primary Key */ Oracle
  • 272332.1 CRS 10g Diagnostic Collection Guide Oracle
  • Remove DOS CR/LFs (^M) Linux/Unix

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme