Important Apps Scripts

/* cmtoday.sql
see info on jobs run today
can specify an individual job name to limit the list
*/
clear columns
set pages 23
set lines 125
col os form A6
col program head "Program Name" form A70 trunc
col shrtnm head "Shortname" format a9
col time head Elapsed form 9999.99
col request_id head "Req Id" form 9999999
col cprogid head "Prg Id" form 999999
col "Started On" format a10
col "Finished On" format a11
col "Submitted By" format a30 trunc
col argument_text head "Arguments" format a50 trunc
col statustxt head Status format a10 trunc
col phasetxt head Phase format a10 trunc
set recsep wrap
set verify off
accept trgtprog char default all prompt 'What is the concurrent program short name : '
accept offsetdays num default 0 prompt 'How many days back (0 for today, 1 for yesterday etc) : '
select a.request_id
,c.concurrent_program_name shrtnm
,c.concurrent_program_id cprogid
,ctl.user_concurrent_program_name "program"
,l2.meaning phasetxt
,l1.meaning statustxt
,to_char(a.actual_start_date,'hh:mi:ssAM') "Started On"
,to_char(a.actual_completion_date,'hh:mi:ssAM') "Finished On"
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,a.argument_text
,u.user_name || ' - ' || u.description "Submitted By"
from APPLSYS.fnd_Concurrent_requests a
,applsys.fnd_user u
,applsys.fnd_lookup_values l1
,applsys.fnd_lookup_values l2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
where u.user_id = a.requested_by
and (upper(c.concurrent_program_name) = upper('&trgtprog') or upper('&trgtprog') = 'ALL')
and trunc(a.actual_start_date) = trunc(sysdate) - &offsetdays
and l1.lookup_type = 'CP_STATUS_CODE'
and l1.lookup_code = a.status_code
and l1.language = 'US'
and l1.enabled_flag = 'Y'
and nvl(l1.start_date_active,sysdate) <= sysdate
and (l1.end_date_active > sysdate or l1.end_date_active is null)
and l2.lookup_type = 'CP_PHASE_CODE'
and l2.lookup_code = a.phase_code
and l2.language = 'US'
and l2.enabled_flag = 'Y'
and nvl(l2.start_date_active,sysdate) <= sysdate
and (l2.end_date_active > sysdate or l2.end_date_active is null)
and a.concurrent_program_id = c.concurrent_program_id
and ctl.concurrent_program_id = c.concurrent_program_id
and ctl.language = 'US'
and a.program_application_id = c.application_id
and ctl.application_id = c.application_id
order by actual_start_date;

/

/* cmlong.sql
monitor script to list the ccmgr jobs running longer than 10 minutes
and exceeding the average historical runtime for this particular
job
*/
col program form A35 trunc head "Program Full Name"
col intprog format a20 trunc head "Internal Name"
col time form 9999.99
col "Req Id" form 9999999
col qname head "Concurrent Manager Queue" format a25 trunc
rem select q.concurrent_queue_name qname
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,a.phase_code,a.status_code
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name ||
' (' || to_char(c.concurrent_program_id) || ')' intprog,
ctl.user_concurrent_program_name "program"
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
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 a.phase_code in ('I','P','R','T')
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 ctl.language = 'US'
and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 10
and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 >
( select avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440
from APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
where c2.concurrent_program_id = c.concurrent_program_id
and a2.concurrent_program_id = c2.concurrent_program_id
and a2.program_application_id = c2.application_id
and a2.phase_code || '' = 'C'
)
order by 5 desc;
/

Daily Total Count Concurrent

select sysdate -1, count(*)
from apps.fnd_concurrent_requests
where to_char(actual_completion_date,'YYYYMMDD') = (select to_char(sysdate -1,'YYYYMMDD') from dual);

/

currently Running Concurrent Rrequests:

SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);

/

prompt To Find Sessions Generating Lots of Redo
prompt

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
AND i.block_changes > 50
ORDER BY 5 desc, 1, 2, 3, 4
/

/* formusers.sql
show the applications sessions currently using a specified form
can optionally limit it by active only
if you need to limit by a user, use appsid.sql
*/
set lines 132
set verify off
set feedback off
col user_name head "Apps Signon" format a12 trunc
col description head "Real Name" format a25 trunc
col user_form_name head "Form Name" format a30 trunc
col time head "Elapsed" format a7
col startedat head "Logon At" format a19
col spid head DBProc format a6
col sidctr head "#Sess" format 99999
col actstat head "Status" format a1
col currform format a30 trunc head CurrentForm
col lastcallet format a11
col sid format 99999
break on startedat on time on user_name on description on sidctr on actstat on spid
accept trgtuser char default ALL prompt 'What is the Apps short username you are interested in : '
accept trgtform char default ALL prompt 'What is the Apps form you are interested in : '
accept trgtstat char default N prompt 'Limit to active users only [y/n] : '
select /*+ rule */
to_char(s.logon_time,'mm/dd hh24:mi:ss') startedat,
a.time,
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "LastCallET",
u.user_name,
u.description ,
count(s.sid) sidctr,
s.sid,
decode(sum(decode(s.status,'ACTIVE',1,0)),0,'I','A') actstat,
p.spid,
a.form_id || ' - ' || a.user_form_name currform
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
-- and l.start_time = u.last_logon_date
-- and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and ((upper(a.user_form_name) like upper('%&trgtform%')) or (upper('&trgtform') = 'ALL'))
and ((s.status = 'ACTIVE' and upper('&trgtstat') = 'Y') or (upper('&trgtstat') != 'Y'))
and ((upper(u.user_name) like upper('%&trgtuser%')) or (upper('&trgtuser') = 'ALL'))
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by to_char(s.logon_time,'mm/dd hh24:mi:ss'),
s.sid,u.user_name, u.description,a.time,a.form_id || ' - ' || a.user_form_name,p.spid,
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60)
order by 8 desc,5,to_char(s.logon_time,'mm/dd hh24:mi:ss'),a.time;
/

Input Request ID and get Output

set long 10000000
select sql_text, sid, SERIAL# from v$session a,v$sqltext b,applsys.fnd_concurrent_requests f
where a.sql_hash_value=b.hash_value
and f.ORACLE_SESSION_ID=a.AUDSID
and f.request_id = '2722865'
/

Input SID and Get output

column osuid format a8
column dbacct format a20
select s.sid, s.process pid, p.spid orapid, s.osuser osuid, s.username dbacct,
s.serial#, s.program, s.machine
from v$session s, v$process p where s.sid = &&1
and p.addr(+) = s.paddr
/


/* showappuser.sql
show the applications user based on the apps logon username
*/

set lines 132
col user_name head "Apps Signon" format a12 trunc
col description head "Real Name" format a25 trunc
col user_form_name head "Form Name" format a35 trunc
col time head "Elapsed Time" format a10
col zoom_level head "Zoom Level"
col startedat head "Logon At" format a19
col spid head DBProc format a6
col svrstat head S format a1 trunc
col elapsedtime head "Elapsed Time" format a12
col program format a30 trunc
break on user_name,description,spid,sid,svrstat nodup
accept trgtusr char prompt 'What is the Apps Logon User ID : '
prompt Here are the sessions for user id &trgtusr....
select to_char(l.start_time,'mm/dd/yy hh:mi:ssAM') startedat,
a.time,
u.user_name, u.description, l.SERIAL#,
p.spid, s.sid,s.status svrstat,
s.program
,a.user_form_name
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where u.user_name = upper('&trgtusr')
and s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
and l.start_time = u.last_logon_date
and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
order by to_char(l.start_time,'mm/dd/yy hh:mi:ssAM');
-- group by u.user_name,u.description,s.sid,s.status,p.spid,s.program
-- group by u.user_name,u.description,s.sid,s.status,p.spid,a.time,a.user_form_name
-- order by to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM'),a.time;

break on elapsedtime nodup
prompt Here are the Apps forms currently in use by &trgtusr.....
select a.user_form_name, max(a.time) elapsedtime
from apps.fnd_signon_audit_view a,
v$process p,
applsys.fnd_logins l,
v$session s,
applsys.fnd_user u
where a.pid = p.pid
and p.addr = s.paddr
and a.user_id = u.user_id
and u.user_name = upper('&trgtusr')
and l.user_id = u.user_id
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by a.user_form_name;

/

/* showappsid.sql
show the applications user based on the sid
*/

rem to_char(max(l.start_time),'mm/dd/yy hh:mi:ssAM') startedat,
set lines 132
set verify off
col user_name head "Apps Signon" format a12 trunc
col description head "Real Name" format a25 trunc
rem col user_form_name head "Form Name" format a30 trunc
col forminfo head "Form Name" format a40 trunc
col time head "Elapsed Time" format a10
col zoom_level head "Zoom Level"
col startedat head "Logon At" format a19
col lastcallet format a11
accept trgtsid number prompt 'What is the SID : '
select /*+ rule */
to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat,
a.time,
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60) "LastCallET",
u.user_name, u.description ,
s.module || ' - ' || a.user_form_name forminfo
from applsys.fnd_logins l,
applsys.fnd_user u,
apps.fnd_signon_audit_view a,
v$process p,
v$session s
where s.sid = &trgtsid
and s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
-- and l.start_time = u.last_logon_date
-- and l.session_number = u.session_number
and l.user_id = u.user_id
and u.user_id = a.user_id
and p.pid = a.pid
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),
floor(s.last_call_et/3600)||':'||
floor(mod(s.last_call_et,3600)/60)||':'||
mod(mod(s.last_call_et,3600),60),
u.user_name, u.description,a.time,s.module || ' - ' || a.user_form_name
order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time;
/

show current per trans

SELECT s.logon_time, t.start_time, oc.sql_text, s.username "User Name",
s.osuser "OS User", s.status "Status", lockwait "Lock Wait",
s.program "Program", s.logon_time "Connect Time", p.program "P Program",
si.physical_reads "Physical Reads", si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets", si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes", s.process "Process",
p.spid, p.pid, s.serial#, si.sid
FROM sys.v_$sess_io si, sys.v_$session s, sys.v_$process p ,
v$open_cursor oc, v$transaction t
WHERE s.username is not null
AND si.sid(+)=s.sid
AND s.sid = oc.sid
AND p.addr(+)=s.paddr
AND s.saddr(+)= t.ses_addr
ORDER BY s.status, si.consistent_gets+si.block_gets desc
/

prompt Temporary Tablespace Usage in terms of alloacted,used,free space
prompt
column tablespace_name format a10 heading "temporary|tablespace"
column allocated_mb format 99999.99 heading "allocated|in mb"
column used_mb format 99999.99 heading "used|in mb"
column free_mb format 99999.99 heading "free|in mb"
select *
from (select a.tablespace_name
, sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name ) x,
(select sum(b.bytes_used/1024/1024) used_mb
, sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name) y
/


set Head off
column USER_NAME format a10
column RESPONSIBILITY_NAME format a20
spool /oracle/scripts/sysadminresponse.log
SELECT A.USER_NAME , B.RESPONSIBILITY_NAME FROM FND_USER A,FND_RESPONSIBILITY_VL B
WHERE B.RESPONSIBILITY_NAME='System Administrator';
spool off
exit;
/