Apps DBA Scripts
To know instance full detalis
SQL>
col instance_name format a13
col host_name format a15
col version format a9
col release format a9
select i.instance_name, i.host_name,
f.release_name release, i.version
from v$instance i,
apps.fnd_product_groups f
where i.instance_name =
f.applications_system_name;
To know
entries in the database :
SQL> select node_name
"Node Name", node_mode "Mode", support_cp
"C",support_web "W", support_admin "A",
support_forms "F" from FND_NODES;
1. Provide Concurrent Program Name, It will list out all
concurrent requests sets names that has concurrent program in it?
SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL
WHERE request_set_id IN
(SELECT request_set_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE concurrent_program_id =
(SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl
WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));
2. Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_tl
WHERE CONCURRENT_PROGRAM_ID IN
(SELECT concurrent_program_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE request_set_id =
(SELECT request_set_id
FROM FND_REQUEST_SETS_TL
WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));
3. Find out Inactive Form sessions for X hours?
set lines 200 pages 200
col event for a30
select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs,
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
a.process, b.spid
from v$session a, v$process b
where a.action like 'FRM%'
and a.paddr = b.addr
and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;
4. Find out Application Names (Products) in Oracle Apps?
SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL
WHERE request_set_id IN
(SELECT request_set_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE concurrent_program_id =
(SELECT CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl
WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));
2. Provide Concurrent Request Set Name, It will list out all the concurrent programs It has?
SELECT USER_CONCURRENT_PROGRAM_NAME
FROM fnd_concurrent_programs_tl
WHERE CONCURRENT_PROGRAM_ID IN
(SELECT concurrent_program_id
FROM FND_REQUEST_SET_PROGRAMS
WHERE request_set_id =
(SELECT request_set_id
FROM FND_REQUEST_SETS_TL
WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));
3. Find out Inactive Form sessions for X hours?
set lines 200 pages 200
col event for a30
select a.sid,a.serial#,a.action,a.status,a.event,round(a.last_call_Et/60/60) LSTCALL_Hrs,
round(a.seconds_in_wait/60/60) SCNDINWAIT_Hrs,
a.process, b.spid
from v$session a, v$process b
where a.action like 'FRM%'
and a.paddr = b.addr
and a.last_call_Et/60/60 > &&No_Of_Hours_Old order by 6,5;
4. Find out Application Names (Products) in Oracle Apps?
set lines 200
set pagesize 300
col APPLICATION_NAME for a70
select a.APPLICATION_NAME, b.APPLICATION_SHORT_NAME from
apps.fnd_application b, apps.fnd_application_tl a where
a.APPLICATION_ID=b.
5. Find out What are all concurrent programs are assigned to What are all concurrent Managers?
set lines 200 pages 300
col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50
break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;
5. Find out What are all concurrent programs are assigned to What are all concurrent Managers?
set lines 200 pages 300
col USER_CONCURRENT_QUEUE_NAME for a50
col CONCURRENT_PROGRAM_NAME for a50
break on USER_CONCURRENT_QUEUE_NAME skip 1;
SELECT C.USER_CONCURRENT_QUEUE_NAME,B.CONCURRENT_PROGRAM_NAME,A.INCLUDE_FLAG
FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT A, APPLSYS.FND_CONCURRENT_PROGRAMS B, APPS.FND_CONCURRENT_QUEUES_VL C
WHERE type_id = b.concurrent_program_id and c.concurrent_queue_id = a.concurrent_queue_id order by C.USER_CONCURRENT_QUEUE_NAME;
6. Find out
session details of a concurrent Request?
set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40
select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id
/
set lines 200 pages 300
col USER_CONCURRENT_PROGRAM_NAME for a40
select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,s.status,logon_time,last_call_et
from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct
where oracle_process_id=p.spid
and s.paddr=p.addr and
ct.concurrent_program_id=c.concurrent_program_id
and request_id=&creq_id
/
7. Find out All
Concurrent Queue/Manager sizes/Processes?
set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;
set lines 200 pages 300
select a.concurrent_queue_name,b.min_processes,b.max_processes from apps.fnd_concurrent_queues a,apps.fnd_concurrent_queue_size b where a.concurrent_queue_id=b.concurrent_queue_id;
8. To know the SID ,Sireal# to find OS level :
SQL> SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT
oracle_process_id
FROM
fnd_concurrent_requests
WHERE
request_id = &request_id);
To Know the SPID :
select
sid,
spid
from v$process p, v$session s
where paddr = addr
and s.sid in(&SID)
order by s.sid;
9. To know CONCURRENTMANAGER completed
SELECT
fcr.REQUEST_ID,fc.USER_CONCURRENT_PROGRAM_NAME,TO_CHAR(fcr.actual_start_date,'DD-MON
HH24:MI:SS') actual_start_date,TO_CHAR(fcr.actual_completion_date,'DD-MON
HH24:MI:SS') actual_completion_date,fcr.STATUS_CODE
FROM FND_CONCURRENT_REQUESTS
fcr,
FND_CONCURRENT_PROGRAMS_TL fc
WHERE fcr.phase_code='C'
AND
fc.concurrent_program_id=fcr.concurrent_program_id
AND
fc.USER_CONCURRENT_PROGRAM_NAME='&CONC_req_name' order by
fcr.actual_start_date ;
10. To find the start & end date of Conc – Req info
:
SELECT
qt.user_concurrent_queue_name
, fcr.Request_Id Request_id
, fu.User_name
, p.spid
, s.sid ||','|| s.serial#
SIDSERIAL
, substr(
Fcpv.Concurrent_Program_Name ||'-'|| Fcpv.User_Concurrent_Program_Name, 1,46)
Program
, to_char(
fcr.actual_start_date, 'mm/dd hh24:mi') actual_start_date
, phase_code, status_code
, to_char( trunc(sysdate) + (
sysdate - fcr.actual_start_date )
, 'hh24:mi:ss' ) duration
FROM apps.Fnd_Concurrent_Queues
Fcq
, apps.fnd_concurrent_queues_tl
qt
, apps.Fnd_Concurrent_Requests
Fcr
, apps.Fnd_Concurrent_Programs
Fcp
, apps.Fnd_User Fu
, apps.Fnd_Concurrent_Processes
Fpro
, v$session s
, v$process p
, apps.Fnd_Concurrent_Programs_Vl
Fcpv
WHERE phase_code = 'C'
AND status_Code = 'X'
AND s.paddr = p.addr
AND fcr.requested_by = user_id
AND fcq.application_id =
qt.application_id
AND fcq.concurrent_queue_id =
qt.concurrent_queue_id
AND userenv('lang') = qt.language
AND fcr.os_process_id =
s.process
AND fcr.Controlling_Manager =
Concurrent_Process_Id
AND (fcq.concurrent_queue_id =
fpro.concurrent_queue_id
AND fcq.application_id =
fpro.queue_application_id )
AND (fcr.concurrent_program_id =
fcp.concurrent_program_id
AND fcr.program_application_id =
fcp.application_id )
AND (fcr.concurrent_program_id =
fcpv.concurrent_program_id
AND fcr.program_application_id =
fcpv.application_id )
ORDER BY fcr.actual_start_date;
11. Req running Duration...
*********************
set linesize 152
col USER_CONCURRENT_PROGRAM_NAME
for a30
col status format a5
col phase format a5
col COMPL_DATE FOR A10
col START_DATE FOR A10
col user_name format a20
col hrs_running format 999.999
select
'*'||a.status_code ||'*' st,
'#'||a.phase_code||'#' ph,
a.USER_CONCURRENT_PROGRAM_NAME,
b.user_name,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE,
(sysdate-actual_start_date)*24
Hrs_Running
from
apps.fnd_conc_req_summary_v a,
apps.fnd_user b
where
--phase_code='C'and
a.REQUESTED_BY=b.user_id and
a.REQUEST_ID =&REQID
order by b.user_name
asc,a.ACTUAL_START_DATE asc ;
12. Long Running Concurrent Manager Requests
select 'ID='|| to_char(r.request_id) job_id,
r.status_code,decode( r.description,null, '* ' ||
p.user_concurrent_program_name,r.description
) description,
f.user_name requested_by,
to_char( trunc(sysdate) + ( sysdate -
r.actual_start_date ), 'hh24:mi:ss' ) duration
,to_char( r.actual_start_date,'mm/dd
hh24:mi:ss' ) actual_start_date,
r.argument_text
from apps.fnd_concurrent_programs_tl p,
apps.fnd_oracle_userid u,
apps.fnd_user f,
apps.fnd_concurrent_requests r
where r.oracle_id = u.oracle_id
and r.requested_by = f.user_id
and r.program_application_id =
p.application_id
and r.concurrent_program_id =
p.concurrent_program_id
and p.language = 'US'
and r.phase_code = 'R' and r.status_code='R'
and decode( r.description,null,
p.user_concurrent_program_name,r.description ) not in ('Adapter
Startup','Gather Schema
Statistics','Planning
Manager','DRWIP Discrete Job Pick List Report')
and (sysdate - r.actual_start_date) *
1440 > &how_long
order by r.request_id
/
13. To find Conc - Reqs particular date Information
on particular date :
set verify off
set feedback off
set pagesize 59
set linesize 180
set recsep off
compute sum of cnt on
actual_start_date;
compute sum of elapsed on
actual_start_date;
compute sum of average on
actual_start_date;
compute sum of waited on
actual_start_date;
compute sum of avewait on
actual_start_date;
column elapsed format 99999.99
heading 'TOTAL|HOURS';
column average format 9999.99
heading 'AVG.|HOURS';
column waited format 99999999.99
heading 'WAITED|HOURS';
column avewait format 9999.99
heading 'AVG.|WAIT';
column cnt format 999,999
heading 'COUNT';
set feedback off
select q.concurrent_queue_name,
count(*)
cnt,
sum(r.actual_completion_date
- r.actual_start_date) * 24 elapsed,
avg(r.actual_completion_date
- r.actual_start_date) * 24 average,
sum(r.actual_start_date
- r.requested_start_date) * 24 waited,
avg(r.actual_start_date
- r.requested_start_date) * 24 avewait
from apps.fnd_concurrent_programs p,
apps.fnd_concurrent_requests
r,
apps.fnd_concurrent_queues
q,
apps.fnd_concurrent_processes
pr
where r.program_application_id = p.application_id and
r.concurrent_program_id
= p.concurrent_program_id and
r.status_code
in ('C','G') and
r.controlling_manager=pr.concurrent_process_id
and
q.concurrent_queue_id=pr.concurrent_queue_id
and
r.concurrent_program_id=p.concurrent_program_id
and
actual_start_date between '04-NOV-2012'
and '07-NOV-2012'
group by
q.concurrent_queue_name;
set feedback on
***********************************
SET PAGES 3000 LINESIZE 400 TRIMS
ON ECHO OFF VERIFY OFF FEEDBACK OFF tab off
col Concurrent_Queue_Name for a9
col User_name for a18
col USER_CONCURRENT_PROGRAM_NAME
for a55
col Concurrent_Program_Name for
a10
Select Concurrent_Queue_Name
Manager,
Request_Id Request, User_name,
fcpt.USER_CONCURRENT_PROGRAM_NAME,
Concurrent_Program_Name Program,
To_Char(Actual_Start_Date,
'DD-MON-YY HH24:MI:SS') Started,
To_Char(Actual_Completion_Date,
'DD-MON-YY HH24:MI:SS') Completed,
round(to_number(to_char(Actual_Completion_Date,'SSSSS')-to_number(to_char(Actual_Start_Date,'SSSSS'))))
time_sec
from Fnd_Concurrent_Queues Fcq,
Fnd_Concurrent_Requests Fcr,
Fnd_Concurrent_Programs Fcp,
Fnd_User Fu, Fnd_Concurrent_Processes
Fpro,FND_CONCURRENT_PROGRAMS_TL
fcpt
where
Phase_Code in ('C','R','P') And
Fcr.Controlling_Manager =
Concurrent_Process_Id And
Fcr.REQUEST_DATE between
to_date('28-JAN-2012 00:00:00', 'DD-MON-YY HH24:MI:SS') and
to_date('28-JAN-2012
23:59:00','DD-MON-YY HH24:MI:SS')
and
(Fcq.Concurrent_Queue_Id =
Fpro.Concurrent_Queue_Id And
Fcq.Application_Id = Fpro.Queue_Application_Id ) And
(Fcr.Concurrent_Program_Id =
Fcp.Concurrent_Program_Id And
Fcr.Program_Application_Id =
Fcp.Application_Id ) And
Fcr.Requested_By = User_Id And
fcp.APPLICATION_ID =
fcpt.APPLICATION_ID and
fcp.CONCURRENT_PROGRAM_ID =
fcpt.CONCURRENT_PROGRAM_ID and
fcpt.LANGUAGE = userenv('LANG')
order by time_sec desc;
+++++++++++++++++
Conc-Req – Long running:
SELECT fcr.phase_code,
DECODE (fcr.phase_code,'C',
'Completed', 'P', 'Pending', 'R', 'Running', 'I', 'Inactive', fcr.phase_code)
phase,
fcr.status_code,
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',
'Terminating',
'U',
'Disabled',
'W', 'Paused',
'X',
'Terminated',
'Z', 'Waiting',
fcr.status_code) status,
request_date,
frt.responsibility_name,fu.user_name,
fcpt.user_concurrent_program_name,
fcr.request_id,fcr.request_date,fcr.requested_start_date,
fcr.ACTUAL_START_DATE,fcr.ACTUAL_COMPLETION_DATE
FROM fnd_concurrent_requests fcr,
fnd_user fu,
fnd_responsibility_tl frt,
fnd_application_tl fat,
fnd_concurrent_programs_tl fcpt
WHERE (fu.user_id = fcr.requested_by)
AND (fat.application_id =
fcr.program_application_id)
AND (fcr.concurrent_program_id =
fcpt.concurrent_program_id)
AND (fcr.responsibility_id =
frt.responsibility_id)
AND fat.LANGUAGE = 'US'
AND frt.LANGUAGE = 'US'
AND fcpt.LANGUAGE = 'US'
and fcr.phase_code='C'
and status_code='C'
and fcr.request_date > sysdate-3
ORDER BY fcr.request_date DESC;
++++++++++++++++++++
14. To find the conc-req id detaisl with trace
details ...
column traceid format a8
column tracename format a80
column
user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80 pages 22 head off
select
'Request id: '||request_id ,
'Trace
id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace
Dir: Go to directory
'||dest.value,
'Search String: ls -al
*'||oracle_process_id||'*',
'Prog. Name:
'||prog.user_concurrent_program_name,
'File
Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status
: '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module
: '||ses.module
from apps.fnd_concurrent_requests
req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm,
apps.fnd_concurrent_programs_vl prog,
apps.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;
EX - Output:
Request id: 22849336
Trace id: 1063 Trace Flag: N
Trace Dir:
Go to directory /d01/oracle/uatdb/11.2.0.3/admin/UAT_uatsdl1/diag/rdbms/uqat/UAT/trace
Search String: ls -al *1063*
Prog. Name: PRC: Update Project
Performance Data
File Name: PJI_PJP_SUM_MAIN.SUMMARIZE
Status : Running-Normal
SID Serial: 8787,55063
Module : PJI_PJP_SUMMARIZE_INCR
TKPROF command :
tkprof aps_ora_22822.trc
aps_ora_22822.txt sys=no explain=apps/apps
sort='(prsela,exeela,fchela)'print=20
No comments:
Post a Comment