Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • cold backup scripts to copy locally Linux/Unix
  • Changing default shell Linux/Unix
  • Changing unix system clock when Oracle database is running. Oracle
  • Korn Shell Arithmatic Linux/Unix
  • How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile) Oracle
  • arch_configUOCIOTTO.ora Oracle
  • tuning commmand for cpu, ip and memory stats Linux/Unix
  • Sequence Resetting Oracle
  • ENQ: KO – FAST OBJECT CHECKPOINT tips Oracle
  • v$backup.status information Oracle
  • Caching sequence in Memory Oracle
  • get_aix_vmstat.ksh Oracle
  • find_string_in_database.sql Oracle
  • Implementation of key based authentications Linux/Unix
  • Oracle10g – Using SQLAccess Advisor (DBMS_ADVISOR) with the Automatic Workload Repository 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

  • Library cahe Latches and internal explaination Oracle
  • find_err.sql for finding errors from dba_errors. Oracle
  • lck.sql Oracle
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • SCRIPT TO LIST RECURSIVE DEPENDENCY BETWEEN OBJECTS UTLDTREE.sql Oracle
  • Good notes on Oracle Events 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
  • metalink all dynamic view reference notes. Oracle
  • SQL_PLAN.sql for checking real execution plan Oracle
  • how to find VIP from ifconfig Linux/Unix
  • SQL Tracker by SID sqltrackerbysid.sql Oracle
  • TOP-N Sql to find Nth max or Top N rows Oracle
  • ORA-00064: object is too large to allocate on this O/S during startup Oracle
  • standard Monitoring – 1 Oracle
  • temp_use.sql diplays usage of temp ts Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme