Oracle Application DBA Documentation: 12/01/2008

/* 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;
/
Posted by Ramesh Mani 1 comments Links to this post
Labels: Dba and apps scripts
Kill long running forms / inactive forms processes
kill_forms_db_long.txt

#!/bin/ksh
# ============================================================================
# Program kill_forms_db_long.ksh - Kill long running/inactive forms processes
# History:
# Date Who Description
# -------- ------------- -----------------------------------
# 04/09/07 Navdeep Saini Initial Creation
# 04/20/07 Navdeep Saini Hard coded the forms: INVTTMTX, INVTVQOH, INVTOTRX
# Usage:
# kill_forms_db_long.ksh [SID]
# ============================================================================
##############################################################
# userror: echos the usage error line and exits with an exit code of 1
#############################################################
# Set environmental variables
#
command=$(print $0 | awk -F/ '{print $NF }')
typeset -xu ORACLE_SID
LOG=${HOME}/local/log/kill_forms_db_long.log
PW=/usr/local/bin/GetPass.ksh
typeset -x HOSTNAME=$(hostname)
typeset -x DATE=$DATE
function userror
{
print "Usage: $command []"
exit 2
}
#############################################################
# check that applmgr is running this script
#
if [[ $(/usr/bin/whoami) != applmgr ]]; then
echo "You must be applmgr to run this script."
exit 1
fi
############################################################
# if there are no command line parameters the script is in error
#
if [[ $# = 0 ]]; then
print "Please enter the proper number of command line parameters"
userror
fi
ORAENV_ASK=NO
typeset -x ORACLE_SID=$1
. /usr/local/bin/db
. ${PW} system
# Check to see if the SYSTEM password is available
file:///C|/blog/kill_forms_db_long.txt (1 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
if [[ -z ${SYSTEM_PASS} ]]; then
print "${PW} does not have the SYSTEM password for $ORACLE_SID"
userror
fi

#############################################################
#Main
#############################################################
sqlplus -s <
system/${SYSTEM_PASS}@${ORACLE_SID}
set lines 200
set head off
column machine format a10
column logon_time format a19
column spid format a10
column process format a10
column module format a18
spool /tmp/kill_forms_db.lst
select '------------------------------'||to_char(sysdate,'mm-dd-yyyy hh24:mi:ss')||'-------------------------------------' Time from dual;
select 'Following user forms session, inactive for more than 60 min, are killed' from dual;
set head on
set pagesize 1000
SELECT
p.spid,
s.process,
s.status,
s.machine,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et/3600 Last_Call_ET,
s.action,
s.module,
s.sid,
s.serial#
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE' order by logon_time;
spool off
file:///C|/blog/kill_forms_db_long.txt (2 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
set head off
set feedback off
spool /tmp/db_sessions.lst
Select
'alter system kill session '''||s.sid||','||s.serial#||''';'
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE';
spool off
spool /tmp/forms_session.sh
SELECT
'remsh '||s.machine||' kill -7 '||s.process
FROM
V\$SESSION s
, V\$PROCESS p
WHERE
s.paddr = p.addr
AND
s.username IS NOT NULL
AND
s.username = 'APPS'
AND
s.osuser = 'applmgr'
AND
s.last_call_et/3600 > 1
and
s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and
s.status='INACTIVE';
spool off
EOF

#cat /tmp/kill_forms_db.lst |awk '{print "alter system kill session ('" $7 "\,'" $8}'
#mail -s "Long running forms sessions in "$HOSTNAME navdeep.saini@echostar.com < /tmp/kill_forms_db.lst

cat /tmp/kill_forms_db.lst >> $LOG

file:///C|/blog/kill_forms_db_long.txt (3 of 4) [5/22/2007 2:34:54 PM]
file:///C|/blog/kill_forms_db_long.txt
cat /tmp/db_sessions.lst >> $LOG
cat /tmp/forms_session.sh >> $LOG
#Check to see if any rows returned and run the kill db and forms
NUMROWS=`cat /tmp/kill_forms_db.lst|grep "no rows selected"`
#echo $NUMROWS
if [[ $NUMROWS != "no rows selected" ]] then
sqlplus -s <
system/${SYSTEM_PASS}@${ORACLE_SID}
@/tmp/db_sessions.lst
EOF
#echo "inside if condition"
sh /tmp/forms_session.sh
cat /tmp/kill_forms_db.lst | mailx -s "Ran kill long running forms in "$ORACLE_SID DBATeam@yourdomain.com
fi
Posted by Ramesh Mani 0 comments Links to this post
Labels: Dba and apps scripts
Database Important Scripts
Hit Ratios

select Round(100*(cg.value+db.value-pr.value)/(cg.value+db.value),2) "Buffer Hit Ratio"
from v$sysstat db, v$sysstat cg, v$sysstat pr
where db.name = 'db block gets'
and cg.name = 'consistent gets'
and pr.name = 'physical reads'
/

Clear the whole content and remove all lines inside the file then :

Code:
cat /dev/null > your_file

Total TBS Size

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc
/

Free Space Size

select tablespace_name, bytes/1024/1024 from dba_free_space
/

Last Analyzed

select max(last_analyzed) from dba_tables
/

TEMP TBS Size

select file_name, sum(bytes)/(1024*1024) from dba_temp_files
group by file_name
/

select tablespace_name, sum(bytes)/(1024*1024) TEMPSIZE from dba_temp_files
group by tablespace_name
/

Script: Listing Memory Used By All Sessions

select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/

SCRIPT: How to Determine the Number of Disk Sorts vs Memory Sorts

select 'INIT.ORA sort_area_size: 'value
from v$parameter
where name like 'sort_area_size'
/

select a.name, value
from v$statname a, v$sysstat
where a.statistic# = v$sysstat.statistic#
and a.name in ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')
/

This script lists all jobs that are currently running in the local database.

select
djr.sid sess,
djr.job jid,
dj.log_user subu,
dj.priv_user secd,
dj.what proc,
to_char(djr.last_date,'MM/DD') lsd,
substr(djr.last_sec,1,5) lst,
to_char(djr.this_date,'MM/DD') nrd,
substr(djr.this_sec,1,5) nrt,
djr.failures fail
from
sys.dba_jobs dj,
sys.dba_jobs_running djr
where
djr.job = dj.job
/

Lists all jobs that have been submitted to run in the local database job queue.

select
job jid,
log_user subu,
priv_user secd,
what proc,
to_char(last_date,'MM/DD') lsd,
substr(last_sec,1,5) lst,
to_char(next_date,'MM/DD') nrd,
substr(next_sec,1,5) nrt,
failures fail,
decode(broken,'Y','N','Y') ok
from
sys.dba_jobs
/

Compile

for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;
select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY','VIEW') AND STATUS ='INVALID';
spool off;

for running the script
@c:\dba\compile.sql

Section – A
This scripts is used to take a index rebuild

The following tables we have to recreate it, (FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)

1) This scripts is used to take a index rebuild on following tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)
(spool the following output)

select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE
a.TABLE_NAME in('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'NO'
union
select 'ALTER INDEX ' a.OWNER '.' b.INDEX_NAME ' REBUILD PARTITION 'b.partition_name' NOLOGGING;'
from dba_indexes a,dba_ind_partitions b
where a.indeX_name = b.index_name
and a.TABLE_NAME in ('FND_CONCURRENT_REQUESTS','FND_FILE_TEMP')
and partitioned= 'YES'

2) ALTER TABLE APPLSYS.FND_CONCURRENT_REQUESTS move;
3) ALTER TABLE APPLSYS.FND_FILE_TEMP move;
3) run the index rebuild outuput scripts.
4) run the gather table statistics for mentioned tables
(FND_CONCURRENT_REQUESTS, FND_FILE_TEMP)

TABLE

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'TABLE'
and (extents +1) >= max_extents;

ALTER TABLE .table STORAGE ( MAXEXTENTS x);

where x is greater than max_extents and lesser than unlimited
(2147483645);

ALTER TABLE .table STORAGE ( MAXEXTENTS UNLIMITED);

INDEX

select segment_name, owner, extents, max_extents
from dba_segments
where segment_type = 'INDEX' and
(extents +1) >= max_extents;

ALTER INDEX .index STORAGE ( MAXEXTENTS integer);

ALTER INDEX .index STORAGE ( MAXEXTENTS UNLIMITED);

Section B

The following script's output is used to run a index rebuild on weekly basis

select 'ALTER INDEX ' OWNER '.' INDEX_NAME ' REBUILD NOLOGGING;'
FROM DBA_INDEXES a
WHERE OWNER NOT IN ('SYS','SYSTEM')
AND a.INDEX_TYPE='NORMAL'
AND NOT EXISTS (SELECT INDEX_NAME FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER NOT IN ('SYS','SYSTEM')
AND INDEX_NAME = a.INDEX_NAME)
AND TABLE_NAME NOT IN (SELECT TABLE_NAME from dba_tables c
where TEMPORARY='Y'
and a.table_name = c.table_name)
ORDER BY OWNER,INDEX_NAME;
/

for
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;

select 'alter ' object_type ' ' OBJECT_NAME ' compile ' ';' from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','VIEW') AND STATUS ='INVALID';
spool off;
for running the script
select 'alter ' 'PACKAGE ' OBJECT_NAME ' compile body' ';' from dba_objects where object_type in ('PACKAGE BODY') AND STATUS ='INVALID';
/

spool runts.sql
select 'alter database datafile '''file_name''''' autoextend on;' from dba_data_files;
/
@runts

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/

select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v_$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')

/

SESSION_WAITS

SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM v$session_wait sw,
v$session s
WHERE s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;
/

To select the username and the process status

select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224

selecta.requested_start_date,a.last_update_date,a.status_code,b.user_name ,a.argument_text from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224

To select the username,process,status,Terminal name using SID

select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
/

To see the currently updated archive log files

SQL>select name from v$archived_log where trunc(completion_time) >= trunc(sysdate)-5;

To find the BDUMP,UDUMP directory

select value from v$parameter where name = 'background_dump_dest'
select value from v$parameter where name = 'user_dump_dest'
select value from v$parameter where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest')
/

Identify the user and session ID for a UNIX process

This quick process identifies the Oracle user and session ID of a UNIX process that is using up a large amount of CPU. It can also be used to help find inefficient queries. This query is placed inside of a script that I pass the UNIX process ID to.

select s.sid, s.username, s.osuser, s.serial#
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &1;
/

Tracing an Oracle session by SID

This code accepts an Oracle session ID [SID] as a parameter and will show you what SQL statement is running in that session and what event the session is waiting for. You simply create a SQL file of the code and run it from the SQL prompt.

prompt Showing running sql statements ...........................

select addr from v$process where spid='8419'

select * from v$session where PADDR='00000003B29F9388'

select a.sid Current_SID, a.last_call_et ,b.sql_text
from v$session a
,v$sqltext b
where a.sid = 14
and a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by a.last_call_et,a.sid,b.piece;

prompt Showing what sql statement is doing.....................

select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,
d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
/

Check all active processes, the latest SQL, and the SQL hit ratio

select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;

Monitoring Oracle processes

select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr
order by s.status,1;

/

Displays concurrent requests that have run times longer than one hour (3600 seconds)

SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,phase_code,status_code,
printer,print_style,description,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-
MON-RRRR')
ORDER BY 2 desc
/

This script will map concurrent manager process information about current concurrent managers.

SELECT proc.concurrent_process_id concproc,
SUBSTR(proc.os_process_id,1,6) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(vproc.spid,1,10) svrproc,
DECODE(proc.process_status_code,'A','Active',
proc.process_status_code) cstat,
SUBSTR(concq.concurrent_queue_name,1,30) qnam,
-- SUBSTR(proc.logfile_name,1,20) lnam,
SUBSTR(proc.node_name,1,10) nnam,
SUBSTR(proc.db_name,1,8) dbnam,
SUBSTR(proc.db_instance,1,8) dbinst,
SUBSTR(vsess.username,1,10) dbuser
FROM fnd_concurrent_processes proc,
fnd_concurrent_queues concq,
v$process vproc,
v$session vsess
WHERE proc.process_status_code = 'A'
AND proc.queue_application_id = concq.application_id
AND proc.concurrent_queue_id = concq.concurrent_queue_id
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+)
ORDER BY proc.queue_application_id,
proc.concurrent_queue_id

Show currently running concurrent requests

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(+);
/

To find the CPU consumption

select ss.sid,w.event,command,ss.value CPU ,se.username,se.program, wait_time, w.seq#, q.sql_text,command
from
v$sesstat ss, v$session se,v$session_wait w,v$process p, v$sqlarea q
where ss.statistic# in
(select statistic#
from v$statname
where name = 'CPU used by this session')
and se.sid=ss.sid
and ss.sid>6
and se.paddr=p.addr
and se.sql_address=q.address
order by ss.value desc,ss.sid
/

Script to show problem tablespaces

SELECT space.tablespace_name, space.total_space, free.total_free,
ROUND(free.total_free/space.total_space*100) as pct_free,
ROUND((space.total_space-free.total_free),2) as total_used,
ROUND((space.total_space-free.total_free)/space.total_space*100) as pct_used,
free.max_free, next.max_next_extent
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 total_space
FROM dba_data_files
GROUP BY tablespace_name) space,
(SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_free, ROUND(MAX(bytes)/1024/1024,2) max_free
FROM dba_free_space
GROUP BY tablespace_name) free,
(SELECT tablespace_name, ROUND(MAX(next_extent)/1024/1024,2) max_next_extent FROM dba_segments
GROUP BY tablespace_name) NEXT
WHERE space.tablespace_name = free.tablespace_name (+)
AND space.tablespace_name = next.tablespace_name (+)
AND (ROUND(free.total_free/space.total_space*100)<> free.max_free)
order by pct_used desc

Oracle space monitoring scripts table space wise
This scripts gives warning indicator for all tablespaces that have less then 90% free space in them (with an asterisk in the last column).

select tbs.tablespace_name,
tot.bytes/(1024*1024) "Total Space in MB",
round(tot.bytes/(1024*1024)- sum(nvl(fre.bytes,0))/(1024*1024),2) "Used in MB",
round(sum(nvl(fre.bytes,0))/(1024*1024),2) "Free in MB",
round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) Pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) Pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
order by 5 desc, 1 ;

Oracle space monitoring scripts (grand total table space)

select
sum(tot.bytes/(1024 *1024))"Total size",
sum(tot.bytes/(1024*1024)-sum(nvl(fre.bytes,0))/(1024*1024)) Used,
sum(sum(nvl(fre.bytes,0))/(1024*1024)) Free,
sum((1-sum(nvl(fre.bytes,0))/tot.bytes)*100) Pct
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/(1024*1024), tot.bytes
/

What's holding up the system?

Poorly written SQL is another big problem. Use the following SQL to determine the UNIX pid:

Select
p.pid, s.sid, s.serial#,s.status, s.machine,s.osuser, p.spid, t.sql_text
From
v$session s,
v$sqltext t,
v$process p
Where
s.sql_address = t.address and
s.paddr = p.addr and
s.sql_hash_value = t.hash_value and
s.sid > 7 and
s.audsid != userenv ('SESSIONID')
Order By s.status,s.sid, s.osuser, s.process, t.piece ;
/

Script to display status of all the Concurrent Managers
select distinct Concurrent_Process_Id CpId, PID Opid,
Os_Process_ID Osid, Q.Concurrent_Queue_Name Manager,
P.process_status_code Status,
To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from Fnd_Concurrent_Processes P, Fnd_Concurrent_Queues Q, FND_V$Process
where Q.Application_Id = Queue_Application_ID
and Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
and Spid = Os_Process_ID
and Process_Status_Code not in ('K','S')
order by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Get current SQL from SGA

select sql_text
from V$session s , V$sqltext t
where s.sql_address=t.address
and sid=
order by piece;

You can find the SID from V$session.

What SQL is running and who is running it?

select a.sid,a.serial#,a.username,b.sql_text
from v$session a,v$sqltext b
where a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by 1,2,b.piece;

---
select decode(sum(decode(s.serial#,l.serial#,1,0)),0,'No','Yes') " ",
s.sid "Session ID",s.status "Status",
s.username "Username", RTRIM(s.osuser) "OS User",
b.spid "OS Process ID",s.machine "Machine Name",
s.program "Program",c.sql_text "SQL text"
from v$session s, v$session_longops l,v$process b,
(select address,sql_text from v$sqltext where piece=0) c
where (s.sid = l.sid(+)) and s.paddr=b.addr and s.sql_address = c.address
group by s.sid,s.status,s.username,s.osuser,s.machine,
s.program,b.spid, b.pid, c.sql_text order by s.status,s.sid

TO FIND THE SORTING DETAILS

SELECT a.sid,a.value,b.name from
V$SESSTAT a, V$STATNAME b
WHERE a.statistic#=b.statistic#
AND b.name LIKE 'sort%'
ORDER BY 1;
/

Long running SQL statements

SELECT s.rows_processed, s.loads, s.executions, s.buffer_gets,
s.disk_reads, t.sql_text,s.module, s.ACTION
FROM v$sql /*area*/ s,
v$sqltext t
WHERE s.address = t.address
AND ((buffer_gets > 10000000) or
(disk_reads > 1000000) or
(executions > 1000000))
ORDER BY ((s.disk_reads * 100) + s.buffer_gets) desc, t.address, t.piece
/
Move a table from one tablespace to another

There are many ways to move a table from one tablespace to another. For example, you can create a duplicate table with dup_tab as select * from original_tab; drop the original table and rename the duplicate table as the original one.

The second option is exp table, drop it from the database and import it back. The third option (which is the one I am most interested in) is as follows.

Suppose you have a dept table in owner scott in the system tablespace and you want to move in Test tablespace.

connect as sys

SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT SYSTEM

Elapsed: 00:00:00.50

You want to move DEPT table from system to say test tablespace.

SQL :> connect scott/tiger
Connected.
SQL :> alter table DEPT move tablespace TEST;

Table altered.

SQL :> connect
Enter user-name: sys
Enter password:
Connected.
SQL :> select table_name,tablespace_name from dba_tables where table_name='DEPT' and owner='SCOTT';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DEPT TEST
/

To compile the procedure

Alter PROCEDURE JA_IN_BULK_PO_QUOTATION_TAXES compile

To compile the form

F60gen userid=apps/metroapps@dev module=
.fmb
output_file=/forms/US/form name.fmx
module_type=form batch=no compile_all=special

/

Run this sql statement to get package version :

select text from user_source where name='&package_name'
and text like '%$Header%';

prompt asks you the package name, in return it gives you two lines
corresponding to specifications and body creation files

You can also get pls version on database by running:

select name , text
from dba_source
where text like '%.pls%'
and line <>views

Sometimes version information is available in view definition.
Try the following sql statement :

col TEXT for a40 head "TEXT"
select VIEW_NAME, TEXT
from USER_VIEWS
where VIEW_NAME = '&VIEW_NAME';

workflow

Run wfver.sql (see §5.e) to get version of workflow packages and views.

Finding active and inactive sessions :

set linesize 132
col started format a15
col osuser format a10
col username format a13
col sid format 99999
select d.physical_reads, p.spid,a.sid, a.serial#, a.username, a.osuser,
TO_CHAR(a.logon_time, 'MM/DD HH24:MI') started, a.sql_hash_value,status
from sys.v_$session a, sys.v_$process p, sys.v_$sess_io d
where a.sid = d.sid and a.paddr = p.addr and a.type <> 'BACKGROUND'
and a.status = 'INACTIVE'
order by a.username,a.logon_time

Select 'alter system kill session '''sid','serial#''';' from
V$session where status='INACTIVE';

select p.spid,s.status,s.username,s.machine,s.sid,s.serial#,s.program,
s.osuser,s.sql_address from v$process p,v$session s
where s.paddr = p.addr and s.sid in(&sid)

Finding Locks

select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.ALL_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc

Finding Blocking sessions :

select l1.sid, ' IS BLOCKING ', l2.sid
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

select s1.username '@' s1.machine ' ( SID=' s1.sid ' ) is blocking '
s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;

-- sessions with highest CPU consumption

SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

-- sessions with the highest time for a certain wait

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

-- sessions with highest DB Time usage

SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et <> (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
db file scattered read
Systemwide Waits:
If the TIME spent waiting for multiblock reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at where BLKS_READ / READS > 1 . (A ratio greater than 1 indicates there are some multiblock reads occuring).

It can also be useful to see which sessions are performing scans and trace them to see if the scans are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file scattered read' and total_waits>0 ORDER BY 3,2;

One can also look at:
Statements with high DISK_READS in Sessions with high table scans blocks gotten in

db file sequential read

Systemwide Waits:IO is a normal activity so you are really interested in unnecessary or slow IO activity. If the TIME spent waiting for IOs is significant then we can determine which segment/s Oracle has to go to disk for. See the "Tablespace IO" and "File IO" sections of the ESTAT or STATSPACK reports to get information on which tablespaces / files are servicing the most IO requests, and to get an indication of the speed of the IO subsystem. If the TIME spent waiting for reads is significant then it can be helpful to determine which segment/s Oracle is performing the reads against. The files where the reads are occuring can be found by looking at .

It can also be useful to see which sessions are performing reads and trace them to see if the IOs are expected or not. This statement can be used to see which sessions may be worth tracing:

SELECT sid, total_waits, time_waited FROM v$session_event WHERE event='db file sequential read' and total_waits>0 ORDER BY 3,2;

One can also look at:
Statements with high DISK_READS in Sessions with high "physical reads" in

Undo Tablespace Check

set linesize 150
col username format a18
col sid format 99999
col object_name format a18
select s.username,s.sid,rn.name,rs.rssize/1024/1024 "UsedSize",rs.status,t.used_ublk,t.used_urec,do.object_name
from V$TRANSACTION t,V$SESSION s,V$ROLLNAME rn,V$ROLLSTAT rs,V$LOCKED_OBJECT lo,DBA_OBJECTS do
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
and t.xidusn = lo.xidusn(+)
and do.object_id = lo.object_id;

Temp Tablespace Check

prompt
prompt +----------------------------------------------------+
prompt TEMP TABLESPACE USAGE BY SESSION
prompt +----------------------------------------------------+

--Temp TS usage by each session:

select b.tablespace
,a.sid,
sum(round(((b.blocks*p.value)/1024/1024),2)) size_mb
from v$session a
,v$sort_usage b
,v$process c
,v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr and
a.paddr=c.addr
group by b.tablespace,a.sid
order by sum(round(((b.blocks*p.value)/1024/1024),2)) desc

Extract the DDL Scripts for the existing database links:

SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;

Please change the oracle directories appropriately:

select 'create or replace directory '||OWNER||'.'||DIRECTORY_NAME|| ' as ' ||''''||DIRECTORY_PATH||''''||';' from DBA_DIRECTORIES;
Posted by Ramesh Mani 1 comments Links to this post
Labels: Dba and apps scripts
Here is the list of Important oracle Apps Scripts
Here is the list of few important tables.
Concurrent Manager

FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

FND

FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

AD / Patches

AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

/* To find the latest application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS "how it is done",BASE_RELEASE_FLAG "Base version" FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

/* Query to find out if any patch except localisation patch is applied or not, if applied, that what all drivers it contain and time of it's application*/

select A.APPLIED_PATCH_ID, A.PATCH_NAME, A.PATCH_TYPE, B.PATCH_DRVIER_ID, B.DRIVER_FILE_NAME, B.ORIG_PATCH_NAME, B.CREATION_DATE, B.PLATFORM, B.SOURCE_CODE, B.CREATIONG_DATE, B.FILE_SIZE, B.MERGED_DRIVER_FLAG, B.MERGE_DATE from AD_APPLIED_PATCHES A, AD_PATCH_DRIVERS B where A.APPLIED_PATCH_ID = B.APPLIED_PATCH_ID and A.PATCH_NAME = ''

/* To know that if the patch is applied successfully, applied on both node or not, start time of patch application and end time of patch application, patch top location , session id ... patch run id */

select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.INSTANCE_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID, A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRVIERS C, AD_APPLIED_PATCHES D where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID and A.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = '')) ORDER BY 3;

/* to find the base application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES where BASE_RELEASE_FLAG = 'Y'

/* To find all available application version */

select ARU_RELEASE_NAME||'.'||MINOR_VERSION||'.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE "when lasted", CASE WHEN BASE_RELEASE_FLAG = 'Y' Then 'BASE VERSION' ELSE 'Upgrade' END "BASE/UPGRADE", ROW_SOURCE_COMMENTS "how it is done" from AD_RELEASES

/* To get file version of any application file which is changed through patch application */

select A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME, max(B.VERSION) from AD_FILES A, AD_FILE_VERSIONS B where A.FILE_ID = B.FILE_ID and B.FILE_ID = 86291 group by A.FILE_ID, A.APP_SHORT_NAME, A.SUBDIR, A.FILENAME

/* To get information related to how many time driver file is applied for bugs */

select * from AD_PATCH_RUN_BUGS where BUG_ID in (select BUG_ID from AD_BUGS where BUG_NUMBER = ''

/* To find latest patchset level for module installed */

select APP_SHORT_NAME, max(PATCH_LEVEL) from AD_PATCH_DRIVER_MINIPKS GROUP BY APP_SHORT_NAME

/* To find what is being done by the patch */

select A.BUG_NUMBER "Patch Number", B. PATCh_RUN_BUG_ID "Run Id",D.APP_SHORT_NAME appl_top, D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B, AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F where A.BUG_ID = B.BUG_ID and B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID and D.FILE_ID = F.FILE_ID and A.BUG_NUMBER = '' and B.PATCH_RUN_BUG_ID = ' < > ' and C.EXECUTED_FLAG = 'Y' GROUP BY A.BUG_NUMBER, B.PATCH_RUN_BUG_ID, D. APP_SHORT_NAME, D>SUBDIR, D.FILENAME, E.ACTION_CODE

/* To find Merged patch Information from database in Oracle Applications */

select bug_number from ad_bugs where bug_id in ( select bug_id from ad_comprising_patches where patch_driver_id =(select patch_driver_id from ad_patch_drivers where applied_patch_id =&n) );

/* Second Query to know, what all has been done during application of PATCH */

Select J.PATCh_NAME, H.APPLICATIONS_SYSTEM_NAME Instance_Name, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME appl_top,D.SUBDIR, D.FILENAME, max(F.VERSION) latest, E.ACTION_CODE action from AD_BUGS A, AD_PATCH_RUN_BUGS B,AD_PATCH_RUN_BUG_ACTIONS C, AD_FILES D, AD_PATCH_COMMON_ACTIONS E, AD_FILE_VERSIONS F, AD_PATCH_RUNS G,
AD_APPL_TOPS H, AD_PATCH_DRIVERS I, AD_APPLIED_PATCHES J where A.BUG_ID = B.BUG_ID and
B.PATCH_RUN_BUG_ID = C.PATCH_RUN_BUG_ID and C.FILE_ID = D.FILE_ID and E.COMMON_ACTION_ID = C.COMMON_ACTION_ID
and D.FILE_ID = F.FILE_ID and G.APPL_TOP_ID = H.APPL_TOP_ID and G.PATCH_DRIVER_ID = I.PATCH_DRIVER_ID and
I.APPLIED_PATCH_ID = J.APPLIED_PATCH_ID and B.PATCH_RUN_ID = G.PATCH_RUN_ID and C.EXECUTED_FLAG = 'Y' and
G.PATCH_DRIVER_ID in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = 'merged'))
GROUP BY J.PATCH_NAME, H.APPLICATIONS_SYSTEM_NAME, H.NAME, I.DRIVER_FILE_NAME, D.APP_SHORT_NAME, D.SUBDIR,
D.FILENAME, E.ACTION_CODE

/* Script to find out Patch level of mini Pack */

Select product_version,patch_level from FND_PROUDCT_INSTALLATIONS where patch_level like '%&shortname%';

Replace short name by name of Oracle Apps Minipack for which you want to find out Patch level . ex.
AD - for Applications DBA
GL - for General Ledger
PO - Purchase Order

Where is Concurrent Manager Node ? If you don't know where is your CM here are few ways to find your it .

SQLPLUS> select node_name from apps.fnd_nodes where support_cp='Y';

Here I am listing Unix/Linux commnads which might be useful while troubleshooting Oracle Apps.

Enable Trace on any Executable to find out whats happening at O.S. Level

truss -eafl -o output.trc -o truss.txt
for example for Apache
truss -eafl -o output.trc -o truss.txt apachectl

This command will trace any system calls and will help you to find out errors.

How to find a "word" or pattern in all files in a directory & subdirectories

find . -name "*" -exec grep -l {} \; -print
for example I want to search for word oracle
find . -name "*" -exec grep -l oracle {} \; -print

How to delete files older than N number of days , Useful to delete old log files

find . -name '*.*' -mtime + -exec rm {} \;
for example if I want to delete all files older than 7 days
find . -name '*.*' -mtime +7 -exec rm {} \;
*Check carefully & run it first from logs or dump directory

How to find a class inside a set of Jar files

for i in 'find .-name *.jar'
do
if test 'jar -tvf $i|grep QualityObject.class'
then
ls $i
fi
done

How to find the symbolic links that point to the old path in your oracle_home and appl_top.

This command is useful in cloning after restore from source to target that symbolic link are not pointing to source.

ls -al `find . -type l` | grep $OLD_PATH

To find all the text files that contains the old path at the UNIX level.

find . -type f -print|grep -v ":"|xargs file|grep "text"|awk '{FS=":"}{print $1}'|xargs grep -il $OLD_PATH

How to Sort files based on Size of file in a Directory
Useful in finding out spaces issues

ls -l | sort -nrk 5 | more

How to check if a Port is listening for any Service

netstat -an | grep $PORTNO

How to schedule a Job in Unix

Use cronjob
crontab -l ( list current jobs in cron)
crontab -e ( edit current jobs in cron )
_1_ _2_ _3_ _4_ _5_ $Job_Name
1 - Minutes (0-59)
2 - Hours ( 0-24)
3 - day of month ( 1- 31 )
4 - Month ( 1-12)
5 - A day of week ( 0- 6 ) 0 -> sunday 1-> monday
e.g. 0 0 1 * 5 Means run job at Midnight on 1st of month & every friday

Profile Options Useful for Oracle Apps DBA

Here is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options, it depends on your implemnetation. I am going going to update more about Profile Options.

Applications Help Web Agent
Applications Servlet Agent
Applications Web Agent
Concurrent: Active Request Limit
Concurrent: Hold Requests
Concurrent: Multiple Time Zones
Concurrent: Report Access Level
Concurrent: Report Copies
Concurrent: Request priority
Database Instance
Enable Security Group
FND: Debug Log Filename
FND: Debug Log Level
Forms Runtime Parameters
Gateway User ID
ICX: Discoverer Launcher
ICX: Forms Launcher
ICX: Report Launcher
ICX: Limit Connect
ICX: Limit time
ICX: Session Timeout
MO Operating Unit
Node Trust Level
RRA: Delete Temporary Files
RRA: Enabled
RRA: Service Prefix
RRA: Maximum Transfer Size
Self Service Personal Home Page Mode
Sign-On: Audit Level
Signon Password Failure Limit
Signon Password Hard to Guess
Signon Password Length
Signon Password No Reuse
Site Name
Socket Listener Port
TCF: Host
TCF: Port
TWO TASK
Viewer: Text

Below you will find commonly asked questions about various component Version in Oracle Applications & way to find them which might be helpful in Apps DBA Job. Most of these are for Unix / Linux.

Q: How to find Oracle Applications Web Server Version ?
Q: How to find Version of Apache used with oracle apps 11i ?

Log to Application tier as Operating System Usually called as APPLMGR ; go to location $IAS_ORACLE_HOME/Apache/Apache/bin and run command
./httpd -version
You will see output like

Server version: Oracle HTTP Server Powered by Apache/1.3.19 (Unix)
Server built: Dec 6 2005 14:59:13 (iAS 1.0.2.2.2 rollup 5)
Which means you are on iAS Version 1.0.2.2.2 with patchset rollup 5 with Apache Version 1.3.19

Server version: Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server

Above is output If you have installed 10g Application Server with 11i

Q: How to find Jinitiator Version ?

Check for file like appsweb_SID_HOSTNAME.cfg under $OA_HTML/bin defined by environment variable FORMS60_WEB_CONFIG_FILE & search for entry like jinit_ver_name , you will see entry like
jinit_ver_name=Version=1,3,1,23
which means Jinitiator version is 1.3.1.23 ; if your version is 1.3.1.18 you will see entry like 1,3,1,18

Q: How to find Forms Version in 11i ?

Login to forms from frontend , on top menu bar of forms click on "Help" & Select "About Oracle Applications" go to "Forms Server " section. You should see entry like below depending on your forms version
Oracle Forms Version : 6.0.8.26.0
Which mean you are on forms version 6.0.8.26 . If you want to know whats your forms patchset level then subtract 9 from fourth digit which means for above case form patchset 17 is applied.

Q: How to find Forms Version in Apps from command Line ?

Enter "f60gen" on Forms Server and check for first line in output like
Forms 6.0 (Form Compiler) Version 6.0.8.26.0 (Production)
This confirms that you are on forms server version 6.0.8.26.0 and patch set 17. ( Patch Set = Fourth Digit - 9)

Q: How to find Version of any file in Oracle Apps 11i ? or
Q: How to find any Reports Version 11i ? or

In Oracle Applications under ad utilities there is utility called as adident Used for Identification purpose or to find out file version use
adident Header
for ex. inorder to to find file version of one AR form i.e. ARXGLCOR.fmx
adident Header ARXGLCOR.fmx
You should see output like
$Header APPSTAND.fmb 115.33 2002/04/04 11:13:40 pkm ship
$ $Header ARXGLCOR.fmb 115.15 2005/01/31 13:48 mraymond ship
Which means above form executable consist of two forms whose version is 115.33 & 115.15 resp. Similarly you can use adident to find version of any report in 11i.

Q: How to find Operation System Version (Unix/Linux) ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?

For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like

64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications

Q: How to find Operating System version ?

For solaris use command
uname -a
You will see output like
For Solaris SunOS servername 5.8 Generic_117350-23 sun4u sparc SUNW,Sun-Fire-V240
For RedHat Linux use command
cat /etc/*release*
You will see output like
Red Hat Enterprise Linux AS release 3 (Taroon Update 6)

Which means you are on Solaris 5.8 or Linux AS 3 resp.

Q: How to find if your Operating System is 32 bit or 64 Bit ?
For solaris use command
isainfo -v
If you see out put like
32-bit sparc applications
That means your O.S. is only 32 bit but if you see output like

64-bit sparcv9 applications
32-bit sparc applications
above means your o.s. is 64 bit & can support both 32 & 64 bit applications

Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.

Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?

execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1

Q. How to find Version of Apps 11i ?

Run following SQL from apps user

SQL> select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME
-----------------------
11.5.10.2
Which means you are on Apps Version 11.5.10.2

Q: Can I run 64 bit application on 32 bit Operating system ?

You can run 32 bit application (like oracle application server, web server, all oracle application server are 32 bit ) on both 32 /64 bit operating system but a 64 bit application like 64 bit database can run only on 64 bit operating system.

Q How to find if your database is 32 bit or 64 bit(Useful in applying Patches) ?

execute "file $ORACLE_HOME/bin/oracle" , you should see output like
/u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1
which means you are on 64 bit oracle
If your oracle is 32 bit you should see output like
oracle: ELF 32-bit MSB executable SPARC Version 1
Now you know what should be bit of patch to download

Q How to Discoverer Version installed with Apps ?

Discoverer with Apps installed in ORACLE_HOME same as 806 is usually 3i or 4i. To find Version login to Application Tier & go to $ORACLE_HOME/discwb4/bin and execute
strings dis4ws | grep -i 'discoverer version'
You should see output like
Discoverer Version:Session 4.1.47.09.00

Q. How to find Workflow Version embedded in Apps 11i ?

Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0

You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.

Q: How to find version of JDK Installed on Apps ?

There might be multiple JDK installed on Operating System . Like JDK 1.3.1, 1.4.2 or 1.5 but in order to find which Version of JDK your Apps is using
Open your Context File $SID_$HOSTNAME.xml under $APPL_TOP/admin and look for variable
JDK_TOP oa_var="s_jdktop" what so ever value assigned against that parameter go to that directory & cd bin & execute command
./java -version so lets assume entry above is /usr/jdk then cd /usr/jdk/bin & ./java -version , you will see output like

java version "1.4.2_10"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_10-b03)
Java HotSpot(TM) Client VM (build 1.4.2_10-b03, mixed mode)
Posted by Ramesh Mani 0 comments Links to this post
Labels: Dba and apps scripts
Concurrent Processing / APPS Reporting Scripts
Concurrent Manager Scripts

Non Destructive Script to Clean Concurrent Manager Tables

CMCLEAN.sql
This script re-sets the flags for requests to completed to allow the Managers to come up
(For all Releases of Apps.)

How To Re-create Concurrent Manager Views

This script details how to Re-create the Concurrent Manager Views
(For all Releases of Apps.)

Concurrent Manager Check Script
CCM.sql
This script will diagnose Concurrent Managers and Concurrent Requests
(For all Releases of Apps.)

Concurrent Manager Check Script
FNDCCMDiagnostic115.sh
This script will diagnose Concurrent Managers and Concurrent Requests
(For Apps. 11.5.3 or higher)

Internal Manager Log Script
ICMLOG.sql
This script will provide the location for Retrieving the Internal Manager Log file
(For For all Releases of Apps.)

Concurrent Requests Scripts
Concurrent Request Log Script
CMLOGS.txt
Script for Retrieving Log Files Associated to a Concurrent Request
(For For all Releases of Apps.)

Concurrent Request Check Script
REQCHECK.sql
Script for diagnosing problematic Concurrent Requests
(For all Releases of Apps.)

Analyze Pending Requests Script
ANALYZEPENDING.sql
Script for diagnosing Pending Concurrent Requests
(For all Releases of Apps.)

Analyze Request Script
ANALYZEREQ.sql
Script for Providing a Detailed Analysis of One Concurrent Request
(For Applications Release 11 and up.)

Who Can Run which Requests
WHOCANRUN.sql
Script that lists Responsibilities that can Run a Given Concurrent Program
(For all Releases of Apps.)

Concurrent Request Diagnostic Script
FNDCCMDiagnostic115.sh
This script will diagnose Concurrent Managers and Concurrent Requests
(For Applications Release 11.5.3 and up.)

Concurrent Request Diagnostic Script
bde_request.sql
Process and Session info for one Concurrent Request
(For Applications Release 11.5 and up.)

Printing Script
FNDPrinterValidation115.sh
This script checks a customer's Printer configuration/setup within Oracle Applications.
(For Applications Release 11.5.3 and up.)

Report Review Agent Script
FNDValidateFNDFS115.sh
This script checks basic setup of the concurrent processing tier report review agent.
(For Applications Release 11.5.3 and up.)

You can download the scripts from metalink.

Concurrent Processing (CP) / APPS Reporting Scripts Doc ID: Note:213021.1

Posted by Ramesh Mani 0 comments Links to this post
Labels: Dba and apps scripts
Find Oracle Apps DBA scripts
Oracle Apps DBA can find scripts to help with the concurrent manager. The concurrent manager is the job-scheduling component of Oracle Apps:
The following SQL scripts located under $FND_TOP/sql are useful when diagnosing concurrent manager problems:

1. afimchk.sql Tells the status of the ICM and PMON method

2. afcmstat.sql Lists active manager processes

3. afrqrun.sql Lists all the running, waiting and terminating requests

4. afrqwait.sql Lists requests that are constrained and waiting for the ICM to release them

5. afrqscm.sql Prints log file name of managers that can run a given request. It can be used to check for possible errors when a request stays in pending status. It requires a request id value.

6. afcmcreq.sql Prints the log file name of the manager that processed the request

7. afrqstat.sql Summary of completed concurrent requests grouped by completion status and execution type. It requires number of days prior to today on which to report parameter.

8. afimlock.sql Lists locks that the ICM is waiting to get

9. afcmrrq.sql Lists managers that currently are running a request

Oracle Application DBA Documentation

Thursday, December 11, 2008

Important Apps Scripts

Sunday, December 7, 2008

Recover an accidentally deleted file when the database is still open.

Performence Tuning Scripts List

Shailesh Gudimalla