How much work done:
*******************
select sid,sofar,totalwork,time_remaining from V$SESSION_LONGOPS where sofar < totalwork;
Blocking sessions:
****************
**** Run below query in SQL Developer and check for Blocking session details ****
Query 1#
********
select a.session_id SID, a.oracle_username, a.os_user_name, a.locked_mode,
b.object_name, b.owner, b.object_type, '**SID ' || l1.sid ||' IS BLOCKING ' || l2.sid || '**' "Blocking Session Details"
from gv$locked_object a, dba_objects b, gv$lock l1, gv$lock l2
where a.object_id = b.object_id and l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
Blocking session Info:
========================
select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
Query 2#
********
set linesize 200
SELECT DECODE(request,0,'Holder: ',' Waiter: ')||sid sess,id1,id2,inst_id, lmode, request,type FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1,request;
Output:
*******
SESS ID1 ID2 INST_ID LMODE REQUEST TY
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------- --
Holder: 973 5177344 629773 2 6 0 TX
Waiter: 9108 5177344 629773 2 0 6 TX
Holder: 9108 19726357 124651 2 6 0 TX
Waiter: 9130 19726357 124651 1 0 6 TX
Waiter: 5959 19726357 124651 2 0 6 TX
Based on SID:
**************
set verify off
set head off
set feedback on
set long 5000
select distinct
'========================================================='||chr(10)||
'Sid , Serial# : '||S.Sid ||' , '||S.Serial# ||Chr(10)||
'Server/Shadow : '||P.Spid ||Chr(10)||
'Client/Foreground : '||S.Process ||Chr(10)||
'Terminal / Machine: '||S.terminal||' / '||S.Machine ||Chr(10)||
'Username........: '||S.Username ||Chr(10)||
'Osuser..........: '||S.Osuser ||Chr(10)||
'Program.........: '||S.Program ||Chr(10)||
'Module..........: '||S.Module ||Chr(10)||
'Status..........: '||S.Status ||Chr(10)||
'Action..........: '||S.Action ||Chr(10)||
'Wait_time.......: '||W.Wait_time ||Chr(10)||
'State ..........: '||W.State ||Chr(10)||
'Wait Event .....: '||W.Event ||Chr(10)||
'Seconds_in_wait.: '||W.Seconds_in_wait ||Chr(10)||
'Pga_alloc....: '|| To_char(P.Pga_alloc_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_used.....: '|| To_char(P.Pga_used_mem/1024/1024 ,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_free.....: '|| To_char(P.Pga_freeable_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_max......: '|| To_char(P.Pga_max_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Lock / Latch.: '|| Nvl(S.Lockwait, 'None')||'/ '||Nvl(P.Latchwait, 'None') ||Chr(10)||
'Blocking Sessi=on: '||s.blocking_session||Chr(10)||
'Blocking Session Status: '|| s.blocking_session_status||Chr(10)||
'Latch Spin...: '|| Nvl(P.Latchspin, 'None') ||Chr(10)||
'Logon Time...: '|| To_char(S.Logon_time, 'Dy Dd-Mon-Yy Hh24:Mi:Ss') ||Chr(10)||
'Last Call....: '|| To_char(Sysdate-(S.Last_call_et/60/60/24), 'Dy Dd-Mon-Yy Hh24:Mi:Ss') || ' -> ' || To_char(S.Last_call_et/60, '99999.0') || ' Mins' || To_char(S.Last_call_et/60/60, '99999.0') || ' Hours' ||To_char(S.Last_call_et/60/60/24, '99.0') || ' Days' ||Chr(10)||
'Sql Address. : '||S.Sql_address ||Chr(10)||
'Sql Hash.... : '||S.Sql_hash_value ||Chr(10)||
'Prev Sql Hash: '||S.Prev_hash_value ||Chr(10)||
'Trans Status : '|| Nvl(T.Status,'None') || Chr(10)||
'Trans Active : '|| Nvl(S.Taddr, 'None')||Chr(10)||
'Undo Generation: '||Nvl(T.Used_ublk,0) || ' Blocks'||Chr(10)||
'Changed Blocks : '||I.Block_changes||' Blocks'||Chr(10)||
'............ Current Sql Statment .................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q.Sql_text,'No Current Sql Statment') ||Chr(10)||
'========================================================='||Chr(10)||
'................ Prev Sql Statment ..................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q2.Sql_text,'No Sql Statment') ||Chr(10)||
'========================================================='
from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2 ,gv$session_wait w ,
gv$transaction t, gv$sess_io i
where s.inst_id = p.inst_id
and p.addr=s.paddr
and s.inst_id = i.inst_id
and s.sid=i.sid
and s.sid=nvl('&sid',s.sid)
and s.inst_id = w.inst_id
and s.sid=w.sid
and p.spid=nvl('&spid',p.spid)
and q.inst_id(+) = s.inst_id
and q.HASH_VALUE(+)=s.sql_hash_value
and q2.inst_id(+) = s.inst_id
and q2.hash_value(+)=s.prev_hash_value
and s.inst_id = t.inst_id(+)
and s.taddr=t.addr(+)
and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To get sid details:
===================
select sid,serial#,inst_id,username,status,machine,program,type,action,event from gv$session where sid=&a;
To get spid:
============
select sid, b.serial#, osuser, b.username,b.program, status, process, paddr, spid from gv$process a,gv$session b where sid = '&sid' and a.addr = b.paddr and b.username != ' ' order by 1;
To get concurrent requests details:(Get spid from above query)
==============================================================
set lines 180
set pages 1000
set verify off
set numwidth 100
undef spid
column req_id format 999999
column OPID format a10
column PPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr
and d.spid=&&spid) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
/
Get the details about the requests using below query.
##QUERY##
set lines 180
set pages 300
col name format a20
col QUEUE for a20
col U_NAME for a20
select fcr.request_id req_id,
substr(fcq.concurrent_queue_name, 1, 20) queue,
to_char(fcr.actual_start_date,'hh24:mi') s_time,
substr(fcr.user_concurrent_program_name, 1, 60) name,
substr(fcr.requestor, 1, 9 ) u_name,
round((sysdate -actual_start_date) *24, 2) elap,
decode(fcr.phase_code,'R','Running','P','Inactive','C','Completed', fcr.phase_code) Phase,
substr(decode( fcr.status_code, 'A', 'WAITING', 'B', 'RESUMING',
'C', 'NORMAL', 'D', 'CANCELLED', 'E', 'ERROR', 'F', 'SCHEDULED',
'G', 'WARNING', 'H', 'ON HOLD', 'I', 'NORMAL', 'M', 'NO MANAGER',
'Q', 'STANDBY', 'R', 'NORMAL', 'S', 'SUSPENDED', 'T', 'TERMINATED',
'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',
'UNKNOWN'), 1, 10)
from
apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
apps.fnd_conc_req_summary_v fcr
where fcp.concurrent_queue_id = fcq.concurrent_queue_id
and fcp.queue_application_id = fcq.application_id
and fcr.controlling_manager = fcp.concurrent_process_id
and fcr.request_id = '&RequstID'
order by request_id ;
select fcr.request_id req_id,
fcr.phase_code||'/'||fcr.status_code sta,
fcr.pid pid,
sess.sid sid,
sess.inst_id inst_id,
fcr.running_time time,
nvl(t.used_urec,0) u_rec,
substrb(fcr.user_desc,1,8) user_desc,
substrb(decode(fcr.pgm_code,
'FNDRSSUB',fcr.pgm_name||'-'||rs.user_request_set_name,
'FNDRSSTG',fcr.pgm_name||'-'||rs.user_request_set_name
||'-'||rss.user_stage_name,
fcr.pgm_name), 1,48) pgm_name
from gv$session sess,
gv$transaction t,
fnd_request_sets_vl rs,
fnd_request_set_stages_vl rss,
(select /*+ ordered
index (r fnd_concurrent_requests_n7)
index (pt fnd_concurrent_programs_tl_u1) */
r.request_id request_id,
r.program_application_id application_id,
c.os_process_id pid,
r.oracle_session_id audsid,
r.concurrent_program_id concurrent_program_id,
p.concurrent_program_name pgm_code,
u.description user_desc,
decode(p.concurrent_program_name,
'FNDRSSUB','SET',
'FNDRSSTG','Set Stage',
pt.user_concurrent_program_name) pgm_name,
r.phase_code phase_code,
r.status_code status_code,
to_char(r.request_date,'yymmdd hh24:mi:ss') request_date, to_char(r.actual_completion_date,'yymmdd hh24:mi:ss') actual_completion_date,
to_char(r.requested_start_date,'yymmdd hh24:mi:ss') requested_start_date,
ceil((nvl(r.actual_completion_date,sysdate)-r.actual_start_date)*1440) running_time,
r.actual_start_date actual_start_date_org,
u.user_id user_id,
u.user_name user_name,
r.argument_text arguments,
decode(p.concurrent_program_name,
'FNDRSSUB',r.argument2,
'FNDRSSTG',r.argument2,
-1) request_set_id,
decode(p.concurrent_program_name,
'FNDRSSTG',r.argument3,
-1) request_set_stage_id
from
fnd_concurrent_requests r,
fnd_concurrent_programs_tl pt,
fnd_concurrent_programs p,
fnd_user u,
fnd_concurrent_processes c
where r.requested_by = u.user_id
and r.program_application_id = pt.application_id
and r.concurrent_program_id = pt.concurrent_program_id
and pt.language = 'US'
and pt.application_id = p.application_id
and pt.concurrent_program_id = p.concurrent_program_id
and r.controlling_manager = c.concurrent_process_id
and r.phase_code = 'R'
and r.status_code = 'R') fcr
where fcr.audsid = sess.audsid(+)
and sess.saddr = t.ses_addr(+)
and fcr.request_set_id = rs.request_set_id(+)
and fcr.request_set_id = rss.request_set_id(+)
and fcr.request_set_stage_id = rss.request_set_stage_id(+)
order by fcr.actual_start_date_org desc, fcr.running_time, fcr.request_id desc;
Total work done:
================
SQL> select sid,serial#,sofar,totalwork from gv$session_longops where sid=1751;
SID SERIAL# SOFAR TOTALWORK
---------- ---------- ---------- ----------
1751 27743 82170 82170
1751 27743 91317 91317
1751 27743 195210 195210
1751 27743 91781 91781
1751 27743 141648 141648
1751 27743 82115 82115
1751 27743 91744 91744
1751 27743 195181 195181
1751 27743 103149 103149
1751 27743 84657 84657
1751 27743 159815 159815
1751 27743 185505 185505
1751 27743 69919 141598
1751 27743 91803 91803
1751 27743 84672 84672
1751 27743 91803 91803
1751 27743 103155 103155
1751 27743 159840 159840
18 rows selected.
SQL >select * from gv$session where process in(select os_process_id from fnd_concurrent_requests where request_id='&request');
Get SID from above sql.
Take that sid and find object_id's from below command
SQL >select * from v$locked_objects where session_id='SID'
Now take all the object_id's and check any other session is locking those object_ids apart from the above SID...
SQL >select * from v$locked_objects where object_id =''
if any session is locking check the status of that session from v$session.
if that is inactive kill that session ... to free up the resource
SQL >alter system kill session 'SID,SERIAL#';
Query 3#
********
select sid, b.serial#, osuser, b.username,b.program, status, process, paddr, spid from gv$process a,gv$session b where sid = '&sid' and a.addr = b.paddr and b.username != ' ' order by 1;
/
SID SERIAL# OSUSER USERNAME PROGRAM STATUS PROCESS PADDR SPID
---------- ---------- ------------------------------ -------- ------------------------------ -------- ------------------------ ---------------- ------------------------
973 27137 appvtasi APPS JDBC Thin Client ACTIVE 114454 0000001F755F05A0 918
973 27137 appvtasi APPS JDBC Thin Client ACTIVE 114454 0000001F755F05A0 8791
select sid,serial#,username,status,machine,program from gv$session where sid=&a;
select INST_ID,SID,OBJECT,TYPE from gv$access where SID=
SELECT segment_name, segment_type, block_id, blocks FROM dba_extents WHERE file_id = &file_no AND ( &block_value BETWEEN block_id AND ( block_id + blocks));
Query 4#
********
SQL> set lines 200
set pages 200
spool DB_Locks.lst
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
spool off
Output:
=======
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------------------------------------------------------- ---------------
370 4401 13493 Idle 1
973 9130 33099 Application 588
Query 5#
********
SQL> select sql_text from v$sql where sql_id='8wtrwg1u27uyy';
*******************
select sid,sofar,totalwork,time_remaining from V$SESSION_LONGOPS where sofar < totalwork;
Blocking sessions:
****************
**** Run below query in SQL Developer and check for Blocking session details ****
Query 1#
********
select a.session_id SID, a.oracle_username, a.os_user_name, a.locked_mode,
b.object_name, b.owner, b.object_type, '**SID ' || l1.sid ||' IS BLOCKING ' || l2.sid || '**' "Blocking Session Details"
from gv$locked_object a, dba_objects b, gv$lock l1, gv$lock l2
where a.object_id = b.object_id and l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;
Blocking session Info:
========================
select 'SID ' || l1.sid ||' is blocking ' || l2.sid blocking from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
Query 2#
********
set linesize 200
SELECT DECODE(request,0,'Holder: ',' Waiter: ')||sid sess,id1,id2,inst_id, lmode, request,type FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1,request;
Output:
*******
SESS ID1 ID2 INST_ID LMODE REQUEST TY
-------------------------------------------------- ---------- ---------- ---------- ---------- ---------- --
Holder: 973 5177344 629773 2 6 0 TX
Waiter: 9108 5177344 629773 2 0 6 TX
Holder: 9108 19726357 124651 2 6 0 TX
Waiter: 9130 19726357 124651 1 0 6 TX
Waiter: 5959 19726357 124651 2 0 6 TX
Based on SID:
**************
set verify off
set head off
set feedback on
set long 5000
select distinct
'========================================================='||chr(10)||
'Sid , Serial# : '||S.Sid ||' , '||S.Serial# ||Chr(10)||
'Server/Shadow : '||P.Spid ||Chr(10)||
'Client/Foreground : '||S.Process ||Chr(10)||
'Terminal / Machine: '||S.terminal||' / '||S.Machine ||Chr(10)||
'Username........: '||S.Username ||Chr(10)||
'Osuser..........: '||S.Osuser ||Chr(10)||
'Program.........: '||S.Program ||Chr(10)||
'Module..........: '||S.Module ||Chr(10)||
'Status..........: '||S.Status ||Chr(10)||
'Action..........: '||S.Action ||Chr(10)||
'Wait_time.......: '||W.Wait_time ||Chr(10)||
'State ..........: '||W.State ||Chr(10)||
'Wait Event .....: '||W.Event ||Chr(10)||
'Seconds_in_wait.: '||W.Seconds_in_wait ||Chr(10)||
'Pga_alloc....: '|| To_char(P.Pga_alloc_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_used.....: '|| To_char(P.Pga_used_mem/1024/1024 ,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_free.....: '|| To_char(P.Pga_freeable_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Pga_max......: '|| To_char(P.Pga_max_mem/1024/1024,'9,999.99') ||' Mb' ||Chr(10)||
'Lock / Latch.: '|| Nvl(S.Lockwait, 'None')||'/ '||Nvl(P.Latchwait, 'None') ||Chr(10)||
'Blocking Sessi=on: '||s.blocking_session||Chr(10)||
'Blocking Session Status: '|| s.blocking_session_status||Chr(10)||
'Latch Spin...: '|| Nvl(P.Latchspin, 'None') ||Chr(10)||
'Logon Time...: '|| To_char(S.Logon_time, 'Dy Dd-Mon-Yy Hh24:Mi:Ss') ||Chr(10)||
'Last Call....: '|| To_char(Sysdate-(S.Last_call_et/60/60/24), 'Dy Dd-Mon-Yy Hh24:Mi:Ss') || ' -> ' || To_char(S.Last_call_et/60, '99999.0') || ' Mins' || To_char(S.Last_call_et/60/60, '99999.0') || ' Hours' ||To_char(S.Last_call_et/60/60/24, '99.0') || ' Days' ||Chr(10)||
'Sql Address. : '||S.Sql_address ||Chr(10)||
'Sql Hash.... : '||S.Sql_hash_value ||Chr(10)||
'Prev Sql Hash: '||S.Prev_hash_value ||Chr(10)||
'Trans Status : '|| Nvl(T.Status,'None') || Chr(10)||
'Trans Active : '|| Nvl(S.Taddr, 'None')||Chr(10)||
'Undo Generation: '||Nvl(T.Used_ublk,0) || ' Blocks'||Chr(10)||
'Changed Blocks : '||I.Block_changes||' Blocks'||Chr(10)||
'............ Current Sql Statment .................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q.Sql_text,'No Current Sql Statment') ||Chr(10)||
'========================================================='||Chr(10)||
'................ Prev Sql Statment ..................: '||Chr(10)||
'========================================================='||Chr(10)|| Nvl(Q2.Sql_text,'No Sql Statment') ||Chr(10)||
'========================================================='
from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2 ,gv$session_wait w ,
gv$transaction t, gv$sess_io i
where s.inst_id = p.inst_id
and p.addr=s.paddr
and s.inst_id = i.inst_id
and s.sid=i.sid
and s.sid=nvl('&sid',s.sid)
and s.inst_id = w.inst_id
and s.sid=w.sid
and p.spid=nvl('&spid',p.spid)
and q.inst_id(+) = s.inst_id
and q.HASH_VALUE(+)=s.sql_hash_value
and q2.inst_id(+) = s.inst_id
and q2.hash_value(+)=s.prev_hash_value
and s.inst_id = t.inst_id(+)
and s.taddr=t.addr(+)
and nvl(s.process,-1) = nvl('&ClientPid',nvl(s.process,-1));
To get sid details:
===================
select sid,serial#,inst_id,username,status,machine,program,type,action,event from gv$session where sid=&a;
To get spid:
============
select sid, b.serial#, osuser, b.username,b.program, status, process, paddr, spid from gv$process a,gv$session b where sid = '&sid' and a.addr = b.paddr and b.username != ' ' order by 1;
To get concurrent requests details:(Get spid from above query)
==============================================================
set lines 180
set pages 1000
set verify off
set numwidth 100
undef spid
column req_id format 999999
column OPID format a10
column PPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id "PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
c.program,d.spid from v$session c, v$process d
where c.paddr=d.addr
and d.spid=&&spid) b,
apps.fnd_concurrent_programs_tl e,
apps.fnd_user f,
v$session_wait g
where a.oracle_process_id=b.spid
and a.concurrent_program_id=e.concurrent_program_id
and e.language='US'
and a.requested_by=f.user_id
and b.sid=g.sid
/
Get the details about the requests using below query.
##QUERY##
set lines 180
set pages 300
col name format a20
col QUEUE for a20
col U_NAME for a20
select fcr.request_id req_id,
substr(fcq.concurrent_queue_name, 1, 20) queue,
to_char(fcr.actual_start_date,'hh24:mi') s_time,
substr(fcr.user_concurrent_program_name, 1, 60) name,
substr(fcr.requestor, 1, 9 ) u_name,
round((sysdate -actual_start_date) *24, 2) elap,
decode(fcr.phase_code,'R','Running','P','Inactive','C','Completed', fcr.phase_code) Phase,
substr(decode( fcr.status_code, 'A', 'WAITING', 'B', 'RESUMING',
'C', 'NORMAL', 'D', 'CANCELLED', 'E', 'ERROR', 'F', 'SCHEDULED',
'G', 'WARNING', 'H', 'ON HOLD', 'I', 'NORMAL', 'M', 'NO MANAGER',
'Q', 'STANDBY', 'R', 'NORMAL', 'S', 'SUSPENDED', 'T', 'TERMINATED',
'U', 'DISABLED', 'W', 'PAUSED', 'X', 'TERMINATED', 'Z', 'WAITING',
'UNKNOWN'), 1, 10)
from
apps.fnd_concurrent_queues fcq,
apps.fnd_concurrent_processes fcp,
apps.fnd_conc_req_summary_v fcr
where fcp.concurrent_queue_id = fcq.concurrent_queue_id
and fcp.queue_application_id = fcq.application_id
and fcr.controlling_manager = fcp.concurrent_process_id
and fcr.request_id = '&RequstID'
order by request_id ;
select fcr.request_id req_id,
fcr.phase_code||'/'||fcr.status_code sta,
fcr.pid pid,
sess.sid sid,
sess.inst_id inst_id,
fcr.running_time time,
nvl(t.used_urec,0) u_rec,
substrb(fcr.user_desc,1,8) user_desc,
substrb(decode(fcr.pgm_code,
'FNDRSSUB',fcr.pgm_name||'-'||rs.user_request_set_name,
'FNDRSSTG',fcr.pgm_name||'-'||rs.user_request_set_name
||'-'||rss.user_stage_name,
fcr.pgm_name), 1,48) pgm_name
from gv$session sess,
gv$transaction t,
fnd_request_sets_vl rs,
fnd_request_set_stages_vl rss,
(select /*+ ordered
index (r fnd_concurrent_requests_n7)
index (pt fnd_concurrent_programs_tl_u1) */
r.request_id request_id,
r.program_application_id application_id,
c.os_process_id pid,
r.oracle_session_id audsid,
r.concurrent_program_id concurrent_program_id,
p.concurrent_program_name pgm_code,
u.description user_desc,
decode(p.concurrent_program_name,
'FNDRSSUB','SET',
'FNDRSSTG','Set Stage',
pt.user_concurrent_program_name) pgm_name,
r.phase_code phase_code,
r.status_code status_code,
to_char(r.request_date,'yymmdd hh24:mi:ss') request_date, to_char(r.actual_completion_date,'yymmdd hh24:mi:ss') actual_completion_date,
to_char(r.requested_start_date,'yymmdd hh24:mi:ss') requested_start_date,
ceil((nvl(r.actual_completion_date,sysdate)-r.actual_start_date)*1440) running_time,
r.actual_start_date actual_start_date_org,
u.user_id user_id,
u.user_name user_name,
r.argument_text arguments,
decode(p.concurrent_program_name,
'FNDRSSUB',r.argument2,
'FNDRSSTG',r.argument2,
-1) request_set_id,
decode(p.concurrent_program_name,
'FNDRSSTG',r.argument3,
-1) request_set_stage_id
from
fnd_concurrent_requests r,
fnd_concurrent_programs_tl pt,
fnd_concurrent_programs p,
fnd_user u,
fnd_concurrent_processes c
where r.requested_by = u.user_id
and r.program_application_id = pt.application_id
and r.concurrent_program_id = pt.concurrent_program_id
and pt.language = 'US'
and pt.application_id = p.application_id
and pt.concurrent_program_id = p.concurrent_program_id
and r.controlling_manager = c.concurrent_process_id
and r.phase_code = 'R'
and r.status_code = 'R') fcr
where fcr.audsid = sess.audsid(+)
and sess.saddr = t.ses_addr(+)
and fcr.request_set_id = rs.request_set_id(+)
and fcr.request_set_id = rss.request_set_id(+)
and fcr.request_set_stage_id = rss.request_set_stage_id(+)
order by fcr.actual_start_date_org desc, fcr.running_time, fcr.request_id desc;
Total work done:
================
SQL> select sid,serial#,sofar,totalwork from gv$session_longops where sid=1751;
SID SERIAL# SOFAR TOTALWORK
---------- ---------- ---------- ----------
1751 27743 82170 82170
1751 27743 91317 91317
1751 27743 195210 195210
1751 27743 91781 91781
1751 27743 141648 141648
1751 27743 82115 82115
1751 27743 91744 91744
1751 27743 195181 195181
1751 27743 103149 103149
1751 27743 84657 84657
1751 27743 159815 159815
1751 27743 185505 185505
1751 27743 69919 141598
1751 27743 91803 91803
1751 27743 84672 84672
1751 27743 91803 91803
1751 27743 103155 103155
1751 27743 159840 159840
18 rows selected.
SQL >select * from gv$session where process in(select os_process_id from fnd_concurrent_requests where request_id='&request');
Get SID from above sql.
Take that sid and find object_id's from below command
SQL >select * from v$locked_objects where session_id='SID'
Now take all the object_id's and check any other session is locking those object_ids apart from the above SID...
SQL >select * from v$locked_objects where object_id =''
if any session is locking check the status of that session from v$session.
if that is inactive kill that session ... to free up the resource
SQL >alter system kill session 'SID,SERIAL#';
Query 3#
********
select sid, b.serial#, osuser, b.username,b.program, status, process, paddr, spid from gv$process a,gv$session b where sid = '&sid' and a.addr = b.paddr and b.username != ' ' order by 1;
/
SID SERIAL# OSUSER USERNAME PROGRAM STATUS PROCESS PADDR SPID
---------- ---------- ------------------------------ -------- ------------------------------ -------- ------------------------ ---------------- ------------------------
973 27137 appvtasi APPS JDBC Thin Client ACTIVE 114454 0000001F755F05A0 918
973 27137 appvtasi APPS JDBC Thin Client ACTIVE 114454 0000001F755F05A0 8791
select sid,serial#,username,status,machine,program from gv$session where sid=&a;
select INST_ID,SID,OBJECT,TYPE from gv$access where SID=
SELECT segment_name, segment_type, block_id, blocks FROM dba_extents WHERE file_id = &file_no AND ( &block_value BETWEEN block_id AND ( block_id + blocks));
Query 4#
********
SQL> set lines 200
set pages 200
spool DB_Locks.lst
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
spool off
Output:
=======
BLOCKING_SESSION SID SERIAL# WAIT_CLASS SECONDS_IN_WAIT
---------------- ---------- ---------- ---------------------------------------------------------------- ---------------
370 4401 13493 Idle 1
973 9130 33099 Application 588
Query 5#
********
SQL> select sql_text from v$sql where sql_id='8wtrwg1u27uyy';
No comments:
Post a Comment