DBA SCRIPTS:
============
Find the trace id from concurrent request -id:
===============================================
SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
'Trace Flag: ' || req.enable_trace,
'Trace Name: '
|| dest.VALUE
|| '/'
|| LOWER (dbnm.VALUE)
|| '_ora_'
|| oracle_process_id
|| '.trc',
'Prog. Name: ' || prog.user_concurrent_program_name,
'File Name: ' || execname.execution_file_name
|| execname.subroutine_name,
'Status : '
|| DECODE (phase_code, 'R', 'Runnin')
|| '-'
|| DECODE (status_code, 'R', 'Normal'),
'SID Serial: ' || ses.SID || ',' || ses.serial#,
'Module : ' || ses.module
FROM fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname
WHERE req.request_id = &REQUEST_ID
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME = 'user_dump_dest'
AND dbnm.NAME = 'db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id = execname.executable_id;
check the status of request-id's:
================================
SELECT distinct(a.request_id), fu.user_name,c.concurrent_program_name short_name, ctl.user_concurrent_program_name concurrent_program_name
,frt.responsibility_name,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) mins
, actual_start_date, actual_completion_date,decode(a.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' , 'Terminating'
, 'U' , 'Disabled'
, 'W' , 'Paused'
, 'X' , 'Terminated'
, 'Z' , 'Waiting'
) status_code
,decode(a.phase_code
, 'C' , 'Completed'
, 'I' , 'Inactive'
, 'P' , 'Pending'
, 'R' , 'Running'
) phase_code,a.oracle_process_id SPID
,a.ARGUMENT_TEXT,a.logfile_name,a.outfile_name, a.completion_text
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
and frt.language='US'
and ctl.language='US'
--and a.request_id=65251237
and ctl.user_concurrent_program_name='Planning Data Pull Worker'
ORDER BY a.actual_start_date DESC;
To check the concurrent prog short name,and full name:
======================================================
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
a.user_concurrent_program_name like 'MSCONTSN';
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
a.user_concurrent_program_name in ('XXMRP0431 Delivery Misses due to Material Shortages [5188]','XXONT0688 Promise date fail safe [3063]');
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
a.user_concurrent_program_name in ('MSCONTSN','MSCWIPSN','MSCWSMSN');
Find full conc-prog name from short name:
=========================================
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
b.concurrent_program_name in ('MSCPDPW');
Remove the files every 10 mints:
================================
10 * * * * find /avdf/audit_collector/<db_name> -name "<db_name>*.aud" -mtime +0 -exec rm -f {} \;
context file by running command :
=================================
grep -i guest $CONTEXT_FILE
To Add Tempfile/resize datafile:
================================
SQL> alter database tempfile '/u01/dbfile/o12c/temp02.dbf' resize 12g;
You can add a data file to a temporary tablespace, as follows:
SQL> alter tablespace temp add tempfile '/u02/dbfile/o12c/temp04.dbf' size 2g;
Gather Schema Statistics%:
===========================
select count(*) from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id and p.user_concurrent_program_name in ( 'OnDemand Gather Schema Statistics', 'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)' ) and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-2);
set pagesize 500
set linesize 500
set pages 1000
set line 132
set head on
select r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text
from apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r where p.concurrent_program_id = r.concurrent_program_id and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%' and r.actual_start_date >= sysdate-14 order by r.requested_start_date;
Scheduled GSS:
===============
select REQUEST_ID, CONCURRENT_PROGRAM_ID ,TO_CHAR (requested_start_date, 'DD-MON-RR HH24:MI') "Start Date" from
fnd_concurrent_requests where requested_start_date > SYSDATE and CONCURRENT_PROGRAM_ID in (select CONCURRENT_PROGRAM_ID from
FND_CONCURRENT_PROGRAMS_TL where USER_CONCURRENT_PROGRAM_NAME LIKE 'Gather Schema Statistics');
RMAN BACKUP STATUS:
===================
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;
to find the request id status, which user running:
==================================================
SELECT distinct t.user_concurrent_program_name,
r.REQUEST_ID,
to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,
decode(r.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','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",substr(u.description,1,25) "Who submitted",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
FROM
apps.fnd_concurrent_requests r ,
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate-30)
--AND r.requested_by=22378
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name='XXONT0688 Promise date fail safe [3063]'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');
10.To find Oracle SID from 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 = &reqid
and r.oracle_session_id = s.audsid(+);
To find sid,serial# for a given concurrent request id?
=======================================================
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b,
gv$process c, gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND
b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';
DBSIZE:
=======
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;
FIND SID,SQL FROM Concurrent Request:
=======================================
SELECT distinct(a.request_id), fu.user_name,ctl.user_concurrent_program_name concurrent_program_name
,frt.responsibility_name,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) mins
, actual_start_date, actual_completion_date,a.phase_code,a.status_code,a.oracle_process_id SPID,x.sid,x.status session_status
,x.event session_event,a.ARGUMENT_TEXT,a.logfile_name,a.outfile_name, a.completion_text
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
,v$session x
,v$process y
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
and x.paddr=y.addr
and a.oracle_process_id=y.spid
and frt.language='US'
and ctl.language='US'
and a.request_id in ('73382167')
ORDER BY a.actual_start_date DESC;
Back of table:
===============
create table XXDBA.fnd_concurrent_requests_bkp13 as
select * from fnd_concurrent_requests;
Blocking:
========
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
SELECT S.SID, S.SQL_ID, S.USERNAME, S.MACHINE from
V$SESSION S WHERE S.SID = (SELECT blocker from
(select a.sid blocker, 'is blocking the session ', b.sid blockee from v$lock a, v$lock b
where a.block =1 and b.request > 0 and a.id1=b.id1 and a.id2=b.id2
)
);
check Free space:
=================
set lines 200 pages 1000
col FILE_NAME for a70
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files order by 1,2;
Add Datafile:
===============
aletr ts TEMP2 add tempfile /fd01/RMX1P/oradata/temp1_01.dbf size 1gb
ALTER TABLESPACE INFA_1_CM_INV_NEW_0009 ADD datafile '/fh02/DEP1S/oradata/INFA_1_CM_INV_NEW_0009_02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 30000m;
TO RUN THE GSS:
===============
FND_STATS.GATHER_TABLE_STATS (
ownname => '"WIP"',
tabname => '"WIP_DISCRETE_JOBS"',
percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE
);
To Last Analyze the table:
==========================
select TABLE_NAME "Table Name",to_char(LAST_ANALYZED,'DD-MON-YY HH24:MI:SS') "Date and Time" from dba_TABLES where lower(TABLE_NAME)='&tname';
select last_analyzed from dba_tables where table_name like 'WIP_DISCRETE_JOBS';
============
Find the trace id from concurrent request -id:
===============================================
SELECT 'Request id: ' || request_id, 'Trace id: ' || oracle_process_id,
'Trace Flag: ' || req.enable_trace,
'Trace Name: '
|| dest.VALUE
|| '/'
|| LOWER (dbnm.VALUE)
|| '_ora_'
|| oracle_process_id
|| '.trc',
'Prog. Name: ' || prog.user_concurrent_program_name,
'File Name: ' || execname.execution_file_name
|| execname.subroutine_name,
'Status : '
|| DECODE (phase_code, 'R', 'Runnin')
|| '-'
|| DECODE (status_code, 'R', 'Normal'),
'SID Serial: ' || ses.SID || ',' || ses.serial#,
'Module : ' || ses.module
FROM fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname
WHERE req.request_id = &REQUEST_ID
AND req.oracle_process_id = proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME = 'user_dump_dest'
AND dbnm.NAME = 'db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id = execname.executable_id;
check the status of request-id's:
================================
SELECT distinct(a.request_id), fu.user_name,c.concurrent_program_name short_name, ctl.user_concurrent_program_name concurrent_program_name
,frt.responsibility_name,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) mins
, actual_start_date, actual_completion_date,decode(a.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' , 'Terminating'
, 'U' , 'Disabled'
, 'W' , 'Paused'
, 'X' , 'Terminated'
, 'Z' , 'Waiting'
) status_code
,decode(a.phase_code
, 'C' , 'Completed'
, 'I' , 'Inactive'
, 'P' , 'Pending'
, 'R' , 'Running'
) phase_code,a.oracle_process_id SPID
,a.ARGUMENT_TEXT,a.logfile_name,a.outfile_name, a.completion_text
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
and frt.language='US'
and ctl.language='US'
--and a.request_id=65251237
and ctl.user_concurrent_program_name='Planning Data Pull Worker'
ORDER BY a.actual_start_date DESC;
To check the concurrent prog short name,and full name:
======================================================
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
a.user_concurrent_program_name like 'MSCONTSN';
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
a.user_concurrent_program_name in ('XXMRP0431 Delivery Misses due to Material Shortages [5188]','XXONT0688 Promise date fail safe [3063]');
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
a.user_concurrent_program_name in ('MSCONTSN','MSCWIPSN','MSCWSMSN');
Find full conc-prog name from short name:
=========================================
select a.user_concurrent_program_name, b.concurrent_program_name from fnd_concurrent_programs_tl a,fnd_concurrent_programs b where
a.concurrent_program_id=b.concurrent_program_id and
b.concurrent_program_name in ('MSCPDPW');
Remove the files every 10 mints:
================================
10 * * * * find /avdf/audit_collector/<db_name> -name "<db_name>*.aud" -mtime +0 -exec rm -f {} \;
context file by running command :
=================================
grep -i guest $CONTEXT_FILE
To Add Tempfile/resize datafile:
================================
SQL> alter database tempfile '/u01/dbfile/o12c/temp02.dbf' resize 12g;
You can add a data file to a temporary tablespace, as follows:
SQL> alter tablespace temp add tempfile '/u02/dbfile/o12c/temp04.dbf' size 2g;
Gather Schema Statistics%:
===========================
select count(*) from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id and p.user_concurrent_program_name in ( 'OnDemand Gather Schema Statistics', 'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)' ) and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-2);
set pagesize 500
set linesize 500
set pages 1000
set line 132
set head on
select r.request_id,r.actual_start_date,r.phase_code,r.status_code,r.actual_completion_date,argument_text
from apps.fnd_concurrent_programs_vl p, apps.fnd_concurrent_requests r where p.concurrent_program_id = r.concurrent_program_id and p.application_id = r.program_application_id
and p.user_concurrent_program_name like '%Gather Schema Statistics%' and r.actual_start_date >= sysdate-14 order by r.requested_start_date;
Scheduled GSS:
===============
select REQUEST_ID, CONCURRENT_PROGRAM_ID ,TO_CHAR (requested_start_date, 'DD-MON-RR HH24:MI') "Start Date" from
fnd_concurrent_requests where requested_start_date > SYSDATE and CONCURRENT_PROGRAM_ID in (select CONCURRENT_PROGRAM_ID from
FND_CONCURRENT_PROGRAMS_TL where USER_CONCURRENT_PROGRAM_NAME LIKE 'Gather Schema Statistics');
RMAN BACKUP STATUS:
===================
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;
to find the request id status, which user running:
==================================================
SELECT distinct t.user_concurrent_program_name,
r.REQUEST_ID,
to_char(r.ACTUAL_START_DATE,'dd-mm-yy hh24:mi:ss') "Started at",
to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss') "Completed at",
decode(r.PHASE_CODE,'C','Completed','I','Inactive','P ','Pending','R','Running','NA') phasecode,
decode(r.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','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",substr(u.description,1,25) "Who submitted",round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60)) Etime
FROM
apps.fnd_concurrent_requests r ,
apps.fnd_concurrent_programs p ,
apps.fnd_concurrent_programs_tl t,
apps.fnd_user u, apps.fnd_conc_req_summary_v v
WHERE
r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
AND r.actual_start_date >= (sysdate-30)
--AND r.requested_by=22378
AND r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID
AND t.concurrent_program_id=r.concurrent_program_id
AND r.REQUESTED_BY=u.user_id
AND v.request_id=r.request_id
--AND r.request_id ='2260046' in ('13829387','13850423')
and t.user_concurrent_program_name='XXONT0688 Promise date fail safe [3063]'
order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-mm-yy hh24:mi:ss');
10.To find Oracle SID from 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 = &reqid
and r.oracle_session_id = s.audsid(+);
To find sid,serial# for a given concurrent request id?
=======================================================
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b,
gv$process c, gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND
b.session_id=d.audsid AND a.request_id = &Request_ID AND a.phase_code = 'R';
DBSIZE:
=======
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;
FIND SID,SQL FROM Concurrent Request:
=======================================
SELECT distinct(a.request_id), fu.user_name,ctl.user_concurrent_program_name concurrent_program_name
,frt.responsibility_name,round((nvl(actual_completion_date,sysdate)-actual_start_date)*1440) mins
, actual_start_date, actual_completion_date,a.phase_code,a.status_code,a.oracle_process_id SPID,x.sid,x.status session_status
,x.event session_event,a.ARGUMENT_TEXT,a.logfile_name,a.outfile_name, a.completion_text
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
,v$session x
,v$process y
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
and x.paddr=y.addr
and a.oracle_process_id=y.spid
and frt.language='US'
and ctl.language='US'
and a.request_id in ('73382167')
ORDER BY a.actual_start_date DESC;
Back of table:
===============
create table XXDBA.fnd_concurrent_requests_bkp13 as
select * from fnd_concurrent_requests;
Blocking:
========
SELECT SID, SQL_ID, USERNAME, BLOCKING_SESSION FROM v$session WHERE BLOCKING_SESSION IS NOT NULL;
SELECT S.SID, S.SQL_ID, S.USERNAME, S.MACHINE from
V$SESSION S WHERE S.SID = (SELECT blocker from
(select a.sid blocker, 'is blocking the session ', b.sid blockee from v$lock a, v$lock b
where a.block =1 and b.request > 0 and a.id1=b.id1 and a.id2=b.id2
)
);
check Free space:
=================
set lines 200 pages 1000
col FILE_NAME for a70
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files order by 1,2;
Add Datafile:
===============
aletr ts TEMP2 add tempfile /fd01/RMX1P/oradata/temp1_01.dbf size 1gb
ALTER TABLESPACE INFA_1_CM_INV_NEW_0009 ADD datafile '/fh02/DEP1S/oradata/INFA_1_CM_INV_NEW_0009_02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 30000m;
TO RUN THE GSS:
===============
FND_STATS.GATHER_TABLE_STATS (
ownname => '"WIP"',
tabname => '"WIP_DISCRETE_JOBS"',
percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
cascade => TRUE
);
To Last Analyze the table:
==========================
select TABLE_NAME "Table Name",to_char(LAST_ANALYZED,'DD-MON-YY HH24:MI:SS') "Date and Time" from dba_TABLES where lower(TABLE_NAME)='&tname';
select last_analyzed from dba_tables where table_name like 'WIP_DISCRETE_JOBS';
No comments:
Post a Comment