Useful Oracle Apps DBA related SQL Scripts

How to find out blocking locks,sessions.

SELECT s.inst_id, NVL (s.username, 'Internal') "Database User", m.SID,
s.serial#, p.spid "DB OS Process", m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username '.' SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment' ) ) "Object",
s.machine "Application Server", s.process "Apps OS process", m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session' ) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name", SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID

How to find out long running Concurrent Requests.


SELECT gv.inst_id, gv.SID, fcr.request_id, fcr.program, fcr.phase,
fcr.status, fcr.actual_start_date "start",
fcr.actual_completion_date "end",
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE) - fcr.actual_start_date )
* 60 * 24, 2 ) TIME, fcqtl.user_concurrent_queue_name "Concurrent Manager", fcr.user_name, fcr.argument_text text, fcr.concurrent_program_id, gvw.event
FROM apps.fnd_amp_requests_v fcr,
gv$session gv,
gv$session_wait gvw,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_processes fcproc
WHERE phase_code = 'R'
AND fcr.oracle_session_id = gv.audsid(+)
AND gv.SID = gvw.SID(+)
AND gv.inst_id = gvw.inst_id(+)
AND fcr.controlling_manager = fcproc.concurrent_process_id
AND fcproc.queue_application_id = fcqtl.application_id
AND fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
AND fcqtl.LANGUAGE = 'US'
ORDER BY 9 DESC

How to find out Concurrent Manager Process SID and SPID

SELECT fcq.concurrent_queue_name "Concurrent manager", fcp.os_process_id "Manager PID", LOWER (SUBSTR (fcp.node_name, 1)) "CM Node", ses.SID, proc.spid, SUBSTR (ins.host_name, 1) "DB Node", ins.instance_name "Instance"
FROM apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
gv$process proc,
gv$session ses,
gv$instance ins
WHERE fcp.process_status_code = 'A'
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcp.oracle_process_id = proc.pid
AND ses.paddr = proc.addr
AND proc.inst_id = ses.inst_id
AND ins.instance_number = ses.inst_id
AND ins.instance_number = proc.inst_id
AND fcq.instance_number = ins.instance_number
AND fcp.instance_number = ins.instance_number
ORDER BY fcq.concurrent_queue_name