Blocking query sessions
how to get PID from SID
sqlId from sid.
sql text from sqlid
how to kill session.
locked object
how to check how many sessions are running for same users.
Blocking session query.
----------------------------
SELECT DECODE (l.BLOCK, 0, 'Waiting', 'Blocking ->') user_status
,CHR (39) || s.SID || ',' || s.serial# || CHR (39) sid_serial
,(SELECT instance_name FROM gv$instance WHERE inst_id = l.inst_id)
conn_instance
,s.SID
,s.PROGRAM
,s.osuser
,s.machine
,DECODE (l.TYPE,'RT', 'Redo Log Buffer','TD', 'Dictionary'
,'TM', 'DML','TS', 'Temp Segments','TX', 'Transaction'
,'UL', 'User','RW', 'Row Wait',l.TYPE) lock_type
--,id1
--,id2
,DECODE (l.lmode,0, 'None',1, 'Null',2, 'Row Share',3, 'Row Excl.'
,4, 'Share',5, 'S/Row Excl.',6, 'Exclusive'
,LTRIM (TO_CHAR (lmode, '990'))) lock_mode
,ctime
--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
,object_name
FROM
gv$lock l
JOIN
gv$session s
ON (l.inst_id = s.inst_id
AND l.SID = s.SID)
JOIN gv$locked_object o
ON (o.inst_id = s.inst_id
AND s.SID = o.session_id)
JOIN dba_objects d
ON (d.object_id = o.object_id)
WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
FROM gv$lock
WHERE request > 0)
ORDER BY id1, id2, ctime DESC;
==================================================================
2)
set lines 200 pages 200
select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
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;
3)
Wait time in seconds
====================
set lines 200 pages 200
col wait_class for a10
col MACHINE for a10
col EVENT for a10
col USERNAME for a10
col MACHINE for a10
col status for a10
select
blocking_session,blocking_instance, inst_id,
sid,
serial#,
USERNAME, status,
SQL_ID,
wait_class,
MACHINE,
seconds_in_wait
from
gv$session
where
blocking_session is not NULL and seconds_in_wait >100
order by
seconds_in_wait;
===============================================================
How to get PID from SID:
------------------------
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
SQLID from SID
-------------------------------------
column plsql_entry_object_id format 99999 heading c1
column plsql_entry_subprogram_id format 99999 heading c2
column plsql_object_id format 99999 heading c3
column plsql_subprogram_id format 99999 heading c4
select t1.sql_id,( select t2.sql_fulltext from v$sql t2 where t1.sql_id = t2.sql_id and t1.sql_child_number = t2.child_number ) sql_fulltext,
( select t2.sql_fulltext
from v$sql t2
where t1.prev_sql_id = t2.sql_id and
t1.prev_child_number = t2.child_number ) prev_sql_fulltext,
plsql_entry_object_id,
plsql_entry_subprogram_id,
plsql_object_id,
plsql_subprogram_id
from v$session t1
where sid = 201 ;
=======================
select sql_text from v$sql where sql_id = 'a7u3m7hf3w3y4' ;
=================================
select distinct(username) from gv$session;
select count(*) , username from gv$session group by username;
Locked object :
TO single database.
------------------
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
To RAC database.
=============
select
c.owner,
c.object_name,
c.object_type,
b.INST_ID,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
b.sid = a.session_id
and
a.object_id = c.object_id;
following error occurs,if there is any locked objects.
Error starting at line : 1 in command -
DROP INDEX sys.TQD_QUEUE_KEY_BM_IDX
Error report -
SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
00054. 00000 - "resource busy and acquire with NOWAIT specified or timeout expired"
*Cause: Interested resource is busy.
*Action: Retry if necessary or increase timeout.
how to get PID from SID
sqlId from sid.
sql text from sqlid
how to kill session.
locked object
how to check how many sessions are running for same users.
Blocking session query.
----------------------------
SELECT DECODE (l.BLOCK, 0, 'Waiting', 'Blocking ->') user_status
,CHR (39) || s.SID || ',' || s.serial# || CHR (39) sid_serial
,(SELECT instance_name FROM gv$instance WHERE inst_id = l.inst_id)
conn_instance
,s.SID
,s.PROGRAM
,s.osuser
,s.machine
,DECODE (l.TYPE,'RT', 'Redo Log Buffer','TD', 'Dictionary'
,'TM', 'DML','TS', 'Temp Segments','TX', 'Transaction'
,'UL', 'User','RW', 'Row Wait',l.TYPE) lock_type
--,id1
--,id2
,DECODE (l.lmode,0, 'None',1, 'Null',2, 'Row Share',3, 'Row Excl.'
,4, 'Share',5, 'S/Row Excl.',6, 'Exclusive'
,LTRIM (TO_CHAR (lmode, '990'))) lock_mode
,ctime
--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status
,object_name
FROM
gv$lock l
JOIN
gv$session s
ON (l.inst_id = s.inst_id
AND l.SID = s.SID)
JOIN gv$locked_object o
ON (o.inst_id = s.inst_id
AND s.SID = o.session_id)
JOIN dba_objects d
ON (d.object_id = o.object_id)
WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE
FROM gv$lock
WHERE request > 0)
ORDER BY id1, id2, ctime DESC;
==================================================================
2)
set lines 200 pages 200
select l1.inst_id,l1.sid, ' IS BLOCKING ', l2.sid,l1.type,l2.type,l1.lmode,l2.lmode,l2.inst_id
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;
3)
Wait time in seconds
====================
set lines 200 pages 200
col wait_class for a10
col MACHINE for a10
col EVENT for a10
col USERNAME for a10
col MACHINE for a10
col status for a10
select
blocking_session,blocking_instance, inst_id,
sid,
serial#,
USERNAME, status,
SQL_ID,
wait_class,
MACHINE,
seconds_in_wait
from
gv$session
where
blocking_session is not NULL and seconds_in_wait >100
order by
seconds_in_wait;
===============================================================
How to get PID from SID:
------------------------
col sid format 999999
col username format a20
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
SQLID from SID
-------------------------------------
column plsql_entry_object_id format 99999 heading c1
column plsql_entry_subprogram_id format 99999 heading c2
column plsql_object_id format 99999 heading c3
column plsql_subprogram_id format 99999 heading c4
select t1.sql_id,( select t2.sql_fulltext from v$sql t2 where t1.sql_id = t2.sql_id and t1.sql_child_number = t2.child_number ) sql_fulltext,
( select t2.sql_fulltext
from v$sql t2
where t1.prev_sql_id = t2.sql_id and
t1.prev_child_number = t2.child_number ) prev_sql_fulltext,
plsql_entry_object_id,
plsql_entry_subprogram_id,
plsql_object_id,
plsql_subprogram_id
from v$session t1
where sid = 201 ;
=======================
select sql_text from v$sql where sql_id = 'a7u3m7hf3w3y4' ;
=================================
select distinct(username) from gv$session;
select count(*) , username from gv$session group by username;
Locked object :
TO single database.
------------------
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
To RAC database.
=============
select
c.owner,
c.object_name,
c.object_type,
b.INST_ID,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects cwhere
b.sid = a.session_id
and
a.object_id = c.object_id;
following error occurs,if there is any locked objects.
Error starting at line : 1 in command -
DROP INDEX sys.TQD_QUEUE_KEY_BM_IDX
Error report -
SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
00054. 00000 - "resource busy and acquire with NOWAIT specified or timeout expired"
*Cause: Interested resource is busy.
*Action: Retry if necessary or increase timeout.
