Importance on SID Locking Sessions

1) To get sql text of a session

break on hash_value
set pagesize 1000
set long 200000
select hash_value, sql_text from v$sqltext where hash_value in  (select sql_hash_value from
v$session where sid = &1)
order by piece
/

2) To get why a session is waiting

col event for a25 word_wrap;
select a.event event, a.seconds_in_wait, s.status from v$session_wait a, v$session s where a.sid=s.sid
and a.sid=&1
/

3) To get session information, you can replace the last condition in where clause as per the requirement ( to sid or client process id or client terminal etc)

select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
s.audsid||chr(10)|| ' DB User / OS User : '||s.username||
' / '||s.osuser||chr(10)|| ' Machine - Terminal : '||
s.machine||' - '|| s.terminal||chr(10)||
' OS Process Ids : '||
s.process||' (Client) '||p.spid||' (Server)'|| ' (Since) '||
to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)||
' Client Program Name : '||s.program||chr(10) ||
 ' Action / Module : '||s.action||' / '||s.module||chr(10) || chr(10) ||
' Wait Status : '||s.event || ' ' || s.seconds_in_wait || ' ' || s.state "Session Info"
from v$process p,v$session s
where p.addr = s.paddr
and p.spid=&1

4) To get lock details with holders and waiters

SELECT lpad('-->',DECODE(a.request,0,0,5),' ')||a.sid sess , a.id1, a.id2, a.lmode
, a.request req, a.type, b.event, b.seconds_in_wait FROM V$LOCK a, v$session_wait b
WHERE a.id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
and a.sid=b.sid ORDER BY id1,request
/

5) To get row information of a session, if the information is changing that means the session is actually active else it means inactive

column name format a30 word_wrapped
column vlu format 999,999,999,999
select b.name, a.value vlu  from v$sesstat a, v$statname b
where a.statistic# = b.statistic# and sid =&1 and a.value != 0
and b.name like '%row%'
/

6) Rollback segment usage by running sessions in the database

set pages 1000
set lines 132
col name format a15 wrap
col username format a10
col module format a12
col space_used format 9,999,999,999,999
select a.username,a.sid,r.name,b.start_time,a.module,a.action, (b.used_ublk * 8192) space_used
from v$session a, v$transaction b,v$rollname r where a.saddr=b.ses_addr
and b.xidusn = r.usn order by 7
/
7) To get the details of long running sessions in the database, it gives info on how much work completed, how much is left and also the time it takes to complete

select SID,TARGET,SOFAR,TOTALWORK,UNITS,TIME_REMAINING,ELAPSED_SECONDS from v$session_longops
/

8) To find the jobs running under all concurrent managers in the instance, used for monitoring if requests are getting backedup under any manager

set linesize 140
col CONTAINER_NAME for a28
col PROCID for 999999
col PROCID for a10
col TARGET for a6
col TARGET for 999
col ACTUAL for a6
col ACTUAL for 999
col ENABLED for a7
col COMPONENT_NAME for a30
col STARTUP_MODE for a12
col COMPONENT_STATUS for a17

select fcq.USER_CONCURRENT_QUEUE_NAME Container_Name,
DECODE(fcp.OS_PROCESS_ID,NULL,'Not Running',fcp.OS_PROCESS_ID) PROCID, fcq.MAX_PROCESSES TARGET,fcq.RUNNING_PROCESSES ACTUAL,fcq.ENABLED_FLAG ENABLED,fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, APPS.FND_CP_SERVICES fcs, APPS.FND_CONCURRENT_PROCESSES fcp,fnd_svc_components fsc where fcq.MANAGER_TYPE = fcs.SERVICE_ID and fcs.SERVICE_HANDLE = 'FNDCPGSC' and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) and fcq.concurrent_queue_id = fcp.concurrent_queue_id(+)
and fcq.application_id = fcp.queue_application_id(+) and fcp.process_status_code(+) = 'A'
and fcq.USER_CONCURRENT_QUEUE_NAME like '%Mail%' order by fcp.OS_PROCESS_ID, fsc.STARTUP_MODE
/

9) To get information on a single concurrent request id

select r.request_id,r.oracle_process_id,r.oracle_session_id,r.os_process_id,
s.sid,s.serial#,s.paddr from fnd_concurrent_requests r,v$session s
where request_id = &1 and r.oracle_session_id = s.audsid(+)
/

10)To find out the Temp Tablespace Usage:

set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB, (total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and i.inst_id=ss.inst_id;

 11)TO FIND OUT THE SID,SPID,CLIENT PID when one of them given

col program for a15
col machine for a15
col terminal for a15
col sid for 9999
col serial# for 9999999
col action for a40
set lines 152
select s.sid,s.serial#,'*'||s.process||'*' Client,p.spid Server,s.sql_address,
s.sql_hash_value,s.username,s.program || s.module,s.action,s.terminal,
s.machine,s.status,--s.last_call_et
s.last_call_et/3600 from gv$session s, gv$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and
p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

12)

REM checking Timing details, Client PID of associated oracle SID
REM ============================================================

set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid) and p.spid=nvl('&spid',p.spid) and
nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));

12)Finding Locks On a Particler Object

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
and c.object_name like 'MTL%';