locks

scripts>locks

 

Ttitle ‘ [ Who is holding locks for more than one minute – V_$LOCKED_OBJECT ALL_OBJECTS V_$SESSION ]’ skip 2
col sid format a5
col locker format a10
col OBJECT_NAME format a20
col LOCKED_MODE format a20
select Lpad(session_id,5) “sid”,SERIAL# “Serial”,substr(OBJECT_NAME,1,20) “Object”,
Lpad(substr(ORACLE_USERNAME,1,10),10) “Locker”,NVL(lockwait,’ACTIVE’) “Wait”,DECODE(LOCKED_MODE,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCLUSIVE’,
6, ‘EXCLUSIVE’, ‘UNKNOWN’) “Lockmode”,
OBJECT_TYPE “Type”
FROM SYS.V_$LOCKED_OBJECT A,SYS.ALL_OBJECTS B,SYS.V_$SESSION c
WHERE A.OBJECT_ID = B.OBJECT_ID AND C.SID = A.SESSION_ID ORDER BY 1 asc, 5 desc;

Ttitle ‘ [ Quien Bloquea a Quien]’ skip 2
select s1.username || ‘@’ || s1.machine
|| ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘
|| s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$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
/

Ttitle ‘ [ Eliminando Sesiones Bloqueantes ]’ skip 2
— Si queremos eliminar sessiones bloqueantes – killing block session – kill block ses

select ‘alter system kill session ”’ ||session_id||’,’||SERIAL#||”’;’ from SYS.V_$LOCKED_OBJECT A,SYS.ALL_OBJECTS B,SYS.V_$SESSION c
where A.OBJECT_ID = B.OBJECT_ID AND C.SID = A.SESSION_ID;

 

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s