Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • FGA Part-I Oracle
  • move_arch_files.ksh /* Good One */ Linux/Unix
  • sql_plan9i.sql Oracle
  • scripts to take listener.log backup Linux/Unix
  • setting prompt display with .profile Linux/Unix
  • S3 Basic info AWS
  • export import with parameter file. Oracle
  • How to analyze statspack or AWR report. Oracle
  • Oracle Support Metalink ID 161818.1 Oracle
  • metalink all dynamic view reference notes. Oracle
  • column level grant syntax Oracle
  • secure crt settings Linux/Unix
  • Formatter Explain plan Output 1 Oracle
  • Implementing Listener Security Oracle
  • How To Transfer Passwords Between Databases (ref note: 199582.1) 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

  • Parallel DML Oracle
  • Updated LCK.SQL file. Oracle
  • cp_filesystem.sql Oracle
  • block_ident.sql Oracle
  • All About Trace Fils Oracle
  • currwait.sql 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • ipcs -l Linux/Unix
  • To Find Orphan OS processes. Linux/Unix
  • get_aix_vmstat.ksh Oracle
  • Running select from V$ views from remote server Linux/Unix
  • Oracle Release Explaination Oracle
  • 272332.1 CRS 10g Diagnostic Collection Guide Oracle
  • Export Import with QUERY Oracle
  • oracle fast start failover best practice Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme