El TecnoBaúl de Kiquenet

Kiquenet boring stories

Posts Tagged ‘locks’

TroubleShooting Oracle–PLSQL: Locks

Posted by kiquenet en 30 noviembre 2015

Issue: Can’t compile a stored procedure when it’s locked

ETL process (dtexeui.exe, DtsDebugHost.exe) call many times to a Package, like it is being used, restricting me from compiling it (the package). 

Monitor de Sesiones de PLSQL Developer

Consultas útiles:

select * from DBA_DDL_LOCKS where name like ‘%PNMKT_CLIENTES%’

select    * from    v$session x where machine like ‘%DS0366%’
 

Question:  In our development environment we need to re-compile packages as we change our code. Once in a while a session will hold onto a package, like it is being used, restricting me from compiling it.   Is there a way to find out what SID is holding on to the package in order for me to kill it?

Answer:  You can query v$session and join into v$sql to see session and SQL information.  This will show the SID for the session that is holding a PL/SQL package:

select
   –x.sid
   *
from
   v$session x, v$sqltext y
where
   x.sql_address = y.address
and  machine like ‘%DS0366%’
–   y.sql_text like ‘%PNMKT_CLIENTES%’;

Esta query te dice los objetos que están bloqueados, de tablas y filas:

SELECT
decode(L.TYPE,’TM’,’TABLE’,’TX’,’Record(s)’) TYPE_LOCK,
decode(L.REQUEST,0,’NO’,’YES’) WAIT,
S.OSUSER OSUSER_LOCKER,
S.PROCESS PROCESS_LOCKER,
S.USERNAME DBUSER_LOCKER,
O.OBJECT_NAME OBJECT_NAME,
O.OBJECT_TYPE OBJECT_TYPE,
concat(‘ ‘,s.PROGRAM) PROGRAM,
O.OWNER OWNER
FROM v$lock l,dba_objects o,v$session s
WHERE l.ID1 = o.OBJECT_ID
AND s.SID =l.SID
AND l.TYPE in (‘TM’,’TX’)

Esta otra te dice si existe algún paquete pillado (no bloqueado):

select /*+ CHOOSE */ a.sid, a.serial#, A.INST_ID,a.username, a.username "DB User",
a.osuser, a.status, a.terminal, a.type ptype, b.owner, b.object, b.type, a.USERNAME "DB User"
from gv$session a, gv$access b
where a.sid=b.sid and a.inst_id = b.inst_id
and b.type<>’NON-EXISTENT’
and (b.owner is not null) and (b.owner<>’SYSTEM’) and (b.owner<>’SYS’)
AND B.OBJECT like ‘NOMBRE_DEL_PAQUETE’
ORDER BY STATUS, OBJECT , TYPE

Si el STATUS te aparece como ACTIVE, ese paquete si lo intentas compilar te dará TIMEOUT y no podrás.

 

— view all currently locked objects:
 
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, ‘Not Blocking’,
  1, ‘Blocking’,
  2, ‘Global’) STATUS,
  DECODE(v.locked_mode,
    0, ‘None’,
    1, ‘Null’,
    2, ‘Row-S (SS)’,
    3, ‘Row-X (SX)’,
    4, ‘Share’,
    5, ‘S/Row-X (SSX)’,
    6, ‘Exclusive’, TO_CHAR(lmode)
  ) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;
 
 
— list current locks
 
SELECT session_id,lock_type,
mode_held,
mode_requested,
blocking_others,
lock_id1
FROM dba_lock l
WHERE lock_type
NOT IN (‘Media Recovery’, ‘Redo Thread’);

 
 
— list objects that have been
— locked for 60 seconds or more:

 
SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program",
s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program",
s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1,
gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 60
AND h.mode_held != ‘None’
AND h.mode_held != ‘Null’
AND w.mode_requested != ‘None’
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert DESC;

References :

http://psoug.org/snippet/LOCKS-View-Locked-Objects_866.htm
http://www.snapdba.com/2013/03/how-to-recompile-a-plsql-package-locked-by-another-user/
http://www.dba-oracle.com/t_session_locking_plsql_package.htm

Oracle Tuning Power Scripts
https://books.google.es/books?id=8qIVRkmM5yMC&pg=PA102&lpg=PA102&dq=DBA_DDL_LOCKS+session_id&source=bl&ots=22xtpcRGZx&sig=QpMBtIHIxJ3awl8vpSdrDixonRI&hl=en&sa=X&ved=0ahUKEwj6roDM4bfJAhWLvRQKHSCODHk4ChDoAQgxMAM#v=onepage&q=DBA_DDL_LOCKS%20session_id&f=false

Books
http://it-ebooks.info/search/?type=title&q=Oracle Tuning

Posted in Oracle | Etiquetado: , | Leave a Comment »