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,

   dba_objects c

where
   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.