Locks on Database
Locks on Database
-----------------
set pages 50000 lines 32767
select
object_name,s.inst_id,s.sid,s.serial#,p.sid,s.osuser,s.server,s.machine,s.status
from gv$locked_object l,gv$session s,gv$process p,dba_objects o
where
l.object_id=o.object_id and
l.session_id=s.sid and
s.paddr=p.addr;
Query to find which is locking the other session:
------------------------------------------------
set pages 50000 lines 32767
col SQL_TEXT for a60
col FIRST_LOAD_TIME a20
select
sesion.sid,sesion.status,sesion.username,sql_text,sqlarea.first_load_time
from gv$sqlarea sqlarea, gv$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.sid=161
and sesion.username is not null;
SID STATUS USERNAME SQL_TEXT FIRST_LOAD_TIME
--- ------ -------- -------- ---------------
To find the Sid and their longops:
--------------------------------
set pages 50000 lines 32767
col OPNAME form a22
select
l.sid,l.serial#,l.OPNAME,l.SOFAR,l.TOTALWORK,l.TIME_REMAINING,l.ELAPSED_SECONDS
from gv$session_longops l,gv$session s where s.sid=l.sid and
s.serial#=l.serial#;
SID SERIAL# OPNAME
SOFAR TOTALWORK TIME_REMAINING
ELAPSED_SECONDS
-------- ---------- -------- ---------- ---------- --------------
---------------
To find the locks for the sid:
-----------------------------
set pages 50000 lines 32767
col OWNER for a20
col NAME for a20
SELECT * FROM dba_dml_locks where SESSION_ID='&sid';
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS
---------- ----- ---- --------- ------------- ------------ ---------------
To find any holding sessions:
----------------------------
select * from dba_blockers;
HOLDING_SESSION
---------------
161
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1
LOCK_ID2
--------------- --------------- --------- --------- -------------- --------
--------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id ||
' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || '
( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from
gv$lock l1, gv$session s1, gv$lock l2, gv$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 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select
(select username from v$session where sid = a.sid ) blocker,
a.sid, 'is blocking ',
(select username from v$session where sid =b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block =1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2; 2 3
4 5 6 7
8 9 10
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
Find locks:
----------
set pages 50000 lines 32767
select 'Sid '||a.sid||' waiting on Sid '||b.sid||' for object '||c.owner||'.'||
c.object_name||' since '||round(d.last_call_et/60)||'
Minutes'||decode(f.status,'INACTIVE','
and Sid '||f.sid||' is inactive since '||round(f.last_call_et/60)||'
Minutes.','.')
from gv$lock a,gv$lock b, gv$session d,dba_objects c,gv$locked_object
e,gv$session f
where a.request!=0 and a.type=b.type
and b.lmode!=0 and b.lmode!=1 and a.id1=b.id1 and a.id2=b.id2 and b.request=0
and b.block=1 and a.sid=d.sid and
d.sid=e.session_id and d.status='ACTIVE' and e.object_id=c.object_id and
b.sid=f.sid order by d.last_call_et desc;
Another way to find locks:
-------------------------
set pages 50000 lines 32767
select l1.sid, ' IS BLOCKING ', l2.sid from gv$lock l1, gv$lock l2
where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
List of blocking session:
-------------------------
set pages 9999 lines 300
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
BLOCKING_SESSION
SID SERIAL# WAIT_CLASS
SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- ---------------
Particular user query:
----------------------
set pages 9999 lines 300
select a.sid, a.serial#, b.sql_text
from v$session a, v$sqlarea b
where a.sql_address=b.address
and a.username='EODSBTCH';
Time since last user activity
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
Sessions sorted by logon time
-----------------------------
set pages 9999 lines 300
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
Show user info including os pid
-------------------------------
set pages 9999 lines 300
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
Show a users current sql
------------------------
set pages 9999 lines 300
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Session status associated with the specified os process id
----------------------------------------------------------
set pages 9999 lines 300
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
ISSUE:
-----
ORA-00054: resource busy and acquire with NOWAIT specified
Locked sessions on Oracle Database Objects :-
-----------------------------------------------
Query 1:
-------
set pages 50000 lines 32767
SELECT s.inst_id,s.sid || ',' || s.serial# sess_id,
oracle_username || ' (' || s.osuser || ')' os_username,
owner || '.' || object_name,object_type,object_id,
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)) LOCK_MODE,
a.sql_text
FROM gv$locked_object v,
dba_objects d,
gv$lock l,
gv$session s
gv$sqlarea a
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
ORDER BY oracle_username, session_id;
Query 2: (More Information)
-------
set pages 50000 lines 32767
select
vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
, DECODE(vlo.LOCKED_MODE,
0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE', NULL)
LOCK_MODE,
do.owner, do.object_name, do.object_type
, vs.saddr, vs.serial#, vs.paddr, vs.username, vs.ownerid, vs.status,
vs.server, vs.schemaname, vs.osuser, vs.machine, vs.program, vs.type,
vs.logon_time, vs.last_call_et, vs.blocking_session_status,
vs.event#, vs.event, vs.wait_class#, vs.wait_class, vs.wait_time,
vs.seconds_in_wait, vs.state
from gv$locked_object vlo
inner join dba_objects do on (vlo.object_id = do.object_id)
left outer join gv$session vs on (vlo.session_id = vs.sid);
Query 3:
-------
set pages 50000 lines 32767
SELECT
b.inst_id,a.session_id,b.serial#,b.STATUS,b.machine,a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE,
c.owner,c.object_name,c.object_type,c.object_id,d.sql_text
FROM
gv$locked_object a,
gv$session b,
dba_objects c,
gv$sqlarea d
WHERE b.sid = a.session_id
AND a.object_id = c.object_id
ORDER BY a.oracle_username, a.session_id;
Query 4:
-------
set pages 50000 lines 32767
SELECT s.inst_id,OS_USER_NAME, ORACLE_USERNAME, s.sid,
o.object_name,o.object_type, s.serial#, a.sql_text
FROM gv$locked_object l, dba_objects o, gv$session s, gv$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;
Command to kill the session:
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';
ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>'; (RAC)
Script to Kill all the locks
----------------------------
set pages 50000 lines 32767
SELECT 'ALTER SYSTEM KILL SESSION
"'||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||"';'
AS "Statement to kill"
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;
SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER,
SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||’.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE, 0,’NONE’,
1,’NULL’,
2,’ROW SHARE’,
3,’ROW EXCLUSIVE’,
4,’SHARE’,
5,’SHARE ROW EXCLUSIVE’,
6,’EXCLUSIVE’,
NULL) LOCK_MODE
FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S,
sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id ;
Or you can do the below :
-------------------------
select * From v$locked_object;
select * From v$session where SID = '<>';
select * from dba_objects where object_id = '<>';
Locked objects :
----------------
select object_name, owner, object_type from dba_objects where object_id in
(select object_id from v$locked_object);
KILL SESSION COMMAND
--------------------
ALTER SYSTEM KILL SESSION 'SID,Serial#' IMMEDIATE;
To find locks on table
----------------------
-- Check if the session has a locked transaction.
SELECT username, sid, serial# FROM v$session WHERE sid IN (SELECT
session_id FROM v$locked_object)
/
-- Query v$lock table from another session and see the different lock modes
select sid, type, lmode, request, block from v$lock where sid in (select
session_id from v$locked_object where object_id=xxx);
select * from v$locked_object
where OBJECT_ID=(select OBJECT_ID from dba_objects where
table_name=<table_name>
and OBJECT_TYPE='TABLE')
select session_id from v$locked_object where object_id=(select object_id
from object_type='TABLE' and owner=' ');
-- Kill the insignificant session.
ALTER SYSTEM KILL SESSION '&SID,&SERIAL'
No comments:
Post a Comment