How to check locks in db by Concurrent program qry

[akumar.oracle]

Version R12/11i? = 11.5.10.2
Instance PROD/TEST/VIS? = PROD
Module? = Financials, HRMS
OS? = HP-UX

In oracle applications how to check the query which the concurrent program is executing and see if it is creating any locks in the database.

regards
Arvin

[sagisunbluez]

Hi,

You check the same by below queries

select
to_char(inst_id) rac_inst_id,
to_char(sid) sid,
to_char(serial#) serial#,
sql_text
from
apps.fnd_concurrent_requests fcr,
gv$session vs,
v$sqltext vq
where
vs.process = fcr.os_process_id
and vs.sql_address = vq.address
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
and request_id in
(select fcr.request_id
from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
fcr1.request_id
from fnd_concurrent_requests fcr1
where 1=1
start with fcr1.request_id = '&request_id'
connect by prior fcr1.request_id = fcr1.parent_request_id) x,
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl
where fcr.request_id = x.request_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcptl.application_id
and fcp.concurrent_program_id = fcptl.concurrent_program_id
and fcptl.language = 'US')
order by serial#;


select s.sid, s.serial#, to_char(logon_time ,'mm/dd hh24:mi:ss') lt,
s.username, s.osuser, p.spid, s.program
from v$session s, v$process p
where s.status = 'ACTIVE'
and s.username is not null
and s.paddr = p.addr
and s.sid = '&SID';


select /*+ ORDERED */ sql_text
from v$session s, v$sqltext t
where t.address = s.sql_address
and s.sid = '&SID'
order by piece



Thanx & Regards

Sagi