@RR to check the long run concurrent:
set pages 1000 lines 1000 pagesize 1000;
col program for a80;
select a.request_id ,decode(a.phase_code,'R','Running','Normal') Phase,
to_char(a.actual_start_date,'DD-MON-RRRR HH24:MI:SS')
Start_time , substr(b.user_concurrent_program_name,1,80) Program
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b
where a.concurrent_program_id = b.concurrent_program_id and
a.status_code = 'R'
order by Start_time;
@RR_SID to find the sid and serial number
set lines 200;
set pages 300;
col program for a50;
SELECT a.request_id, substr(e.user_concurrent_program_name,1,50) Program,d.sid, d.serial# ,d.status,to_char(d.logon_time,'DD-MON-YY HH24:MI:SS')logon, round(d.last_call_et/60) LCT
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,v$session d,
apps.fnd_concurrent_programs_tl e
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id=e.concurrent_program_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.phase_code = 'R'
--and d.sql_id='fnpyvpk41nd5s'
order by 7 desc;
@RR.det to check the parameter of concurrent;
set lines 200;
set pages 1000;
col program for a40;
col user_name for a20;
select a.request_id ,decode(a.phase_code,'R','Running','Normal') Phase,
to_char(a.actual_start_date,'DD-MON-RRRR HH24:MI:SS')
Start_time , substr(b.user_concurrent_program_name,1,40) Program, substr(a.argument_text,1,60) argument, c.user_name
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_user c
where a.concurrent_program_id = b.concurrent_program_id
and a.status_code = 'R'
and a.requested_by=c.user_id
order by Start_time;
@prerun to check the parameter of concurrent completed sucessfully
set lines 200;
set pages 1000;
col user_name for a20;
col argument_text for a60;
SELECT a.request_id, To_Char(a.actual_start_date,'DD-MON HH24:MI') START_DATE,
To_Char(a.actual_completion_date,'DD-MON HH24:MI') END_DATE, round(((a.actual_completion_date-a.actual_start_date)*24*60*60/60),2) AS Process_time,
a.phase_code P,a.status_code S, a.argument_text,d.user_name
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b ,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id= b.concurrent_program_id AND
b.concurrent_program_id=c.concurrent_program_id AND
a.requested_by =d.user_id AND
trunc(a.actual_completion_date) >= trunc(sysdate-7) AND
c.USER_CONCURRENT_PROGRAM_NAME like '%&Concurrent_program_name%'
order by start_date desc;
@sess_io to check the session block changing
select * from v$sess_io where sid=&sid;
lock on table:
select c.owner,c.object_name,c.object_type,c.status,b.sid,b.serial#,b.status,b.osuser,b.machine,a.process,a.locked_mode
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
block Session
SELECT
blocking_session "BLOCKING_SESSION",
sid "BLOCKED_SESSION",
serial# "BLOCKED_SERIAL#",
seconds_in_wait/60 "WAIT_TIME(MINUTES)"
FROM v$session
WHERE blocking_session is not NULL
ORDER BY blocking_session;
Block session with details:
select a.process,a.sql_id,a.sid,a.serial#,a.blocking_session,a.final_blocking_session,to_char(a.logon_time,'DD-MON-YY HH24:MI:SS')logon,round(a.last_call_et/60) LCT,a.username,a.status,a.machine,a.program,a.module,a.action,a.event,a.client_identifier,sql_text
from gv$sqlarea sqlarea, gv$session a
where a.sql_hash_value= sqlarea.hash_value(+)
and a.sql_address= sqlarea.address(+)
and a.username is not null
---and a.status = 'ACTIVE'
--and a.module like '%SQL De%'
and a.sid in ('1050','')
--and a.client_identifier='JDESAI'
--and sql_text='begin FND_CP_OPP_REQ.postprocess (:reqid, :groupid, :flag, :errmsg); end;'
--and a.module ='e:MSC:cp:po/POXRSR'
and a.type='USER'
order by 8 desc;
