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'
/
No comments:
Post a Comment