DB Scripts
/* Datafiles and Backup Status# Purpose : This script list details of datafiles that are sorted in
# the order of their backup mode .
# Input :
# Output : tablespace name,status,time,size in mb
# */
set pages 999 lines 120
@/oracle/admin/dba_scripts/nlsdt
select tablespace_name, b.status, time, round(sum(bytes)/1024/1024) “Size (MB)”
from v$backup b, dba_data_files
where file_id = file#
group by tablespace_name, b.status, time
order by b.status, time
/
/* Get Segment Name from file_id and Block_id
#
# Purpose : This script list segment name of the database objects
# occupying between the blocks in a given datafile .
# Input : file id , block#
# Output : segment name
# */
select segment_name from dba_extents
where &block# between block_id and block_id + blocks
and file_id = &fileid
/
/* List datafiles and sizes for a Tablespace
# Purpose : This script give tablespace name and the total size allocated in MB for a
# given tablespace name .
# Input : tablespace name
# Output : tablespace name,file name,size
# */
set pages 100
col file_name format a40
col ts format a10
compute sum of MB on tablespace_name
select tablespace_name TS,file_name,file_id,bytes/1024/1024 MB from dba_data_files
where tablespace_name = upper(‘&tsname’)
/
/* List db link name, userid and Password
# Purpose : This script gives the database link name , host, password of all the
# database links in that particular database .
# Input :
# Output :db link name,host, userid, password
# */
col name for a25
col host for a10
col userid for a15
col password for a15
select name, host, userid, password from sys.link$
order by name
/
/* List Tables with Zero rows and Empty Blocks > 10000
# Purpose : This script list the table details with zero rows and
# empty blocks > 10000.
# Input :
# Output : owner,table name,empty blocks
# */
select owner, table_name, empty_blocks, empty_blocks*8/1024
from dba_tables
where num_rows = 0 and empty_blocks > 10000
/
/* Free Space in DataFiles for all Tablespaces
# Purpose : This script list the tablespace name , data filename , block id ,
# free space,blocks for each tablespace in the database .
# Input :
# Output : tablespace name,file name,block id,bytes,blocks
# */
select f.tablespace_name, d.file_name, f.block_id, f.bytes, f.blocks
from dba_free_space f, dba_data_files d
where f.file_id=d.file_id
/
/* Get Free Space Details in Chunks
# Purpose : This script list the number of 1MB free space chunks available for a
# given tablespace .
# Input : tablespace name
# Output : count,size
# */
select count(*), bytes/1024/1024 from dba_free_space
where tablespace_name = upper(‘&tblsp’)
group by bytes/1024/1024
order by 2
/
/* List Session Details for Excessive Full Table Scans
# Purpose : This script list the oracle session id , process , program , process id ,
# value for statistic#=140 and value > 100 . This can be interperted as the
# oracle process running with full table scans .
# Input :
# Output : sid,process,program ,spid,value
# */
col program format a30;
select s.sid,s.process,s.program program,p.spid,st.value FTScans
from v$session s, v$process p, v$sesstat st
where
p.addr=s.paddr
and st.sid = s.sid
and st.statistic#=140
and st.value > 100
order by FTScans Desc
/
/* List Running Conc Requests with Excessive Full Table Scans
#
# Purpose : This script list the concurrent request id ,oracle session id ,
# process , program , process id , value for statistic#=140 ,
# value > 100 and phase code is ‘R’ . This can be interperted as
# the concurrent requests running with full table scans .
# Input :
# Output : request_id,sid,program,value
# */
col reqid format 999999;
select f.request_id reqid,s.sid,s.program,st.value FTScans
from v$session s, v$process p, apps.fnd_concurrent_requests f,v$sesstat st
where f.oracle_process_id = p.spid
and p.addr=s.paddr
and st.sid = s.sid
and st.statistic#=140
and st.value > 10
and f.phase_code = ‘R’
order by FTScans Desc
/
/* Fragmentation in Shared Pool
# Purpose : This script gives the latch statistics .
# Input :
# Output : size , status
# */
select sum(ksmchsiz) bytes, ksmchcls status
from sys.x$ksmsp
group by ksmchcls;
/* Find Who is Using a given Package
# Purpose : This script gives the statistics for a given database package.
# Input :
# Output : kglpnmod, kglpnreq, username, kglnaobj, kglobtyp
# */
select a.kglpnmod, a.kglpnreq, b.username, c.kglnaobj, c.kglobtyp
from x$kglpn a, v$session b, x$kglob c
where a.kglpnuse = b.saddr
and upper (c.kglnaobj) like upper (‘%&pkgnm%’)
and a.kglpnhdl = c.kglhdadr
/
/* Query V$LOCK table
#
# Purpose : This script gives the details on locks held in the database and who are
# waiting for the locks to be released .
# Input :
# Output : addr, kaddr, sid, type, id1, id2, lmode, request, ctime, block,
# waiting_session, holding_session, lock_type, mode_held,
# mode_requested, lock_id1, lock_id2
# */
select * from v$lock where request != 0
order by ctime
/
column LOCK_TYPE for a12
column MODE_HELD for a10
column MODE_REQUESTED for a10
column LOCK_ID1 for a10
column LOCK_ID2 for a10
select * from dba_waiters
/
/* Sessions Holding Locks and Sessions Waiting For Locks
#
# Purpose : This script gives the details on locks held in the database and who are
# waiting for the locks to be released .
# Input :
# Output : waiting_session,program,holding_session,ctime,type,mode_held,mode_requested
# */
set pages 999 linesize 120
col mode_requested for a10 heading “LCK REQ”
col mode_held for a10 heading “LCK HLD”
col waiting_session for 99999 heading “WAIT SID”
col holding_session for 99999 heading “HOLD SID”
select /*+ORDERED */
w.waiting_session, substr(wp.program,1,20) waiting_program, w.holding_session, substr(hp.program,1,20) holding_program, l.ctime, l.type, w.mode_held, w.mode_requested
from v$lock l, dba_waiters w,v$session wp,v$session hp
where w.waiting_session = l.sid
and l.request != 0
and w.waiting_session = wp.sid
and w.holding_session = hp.sid
order by ctime
/
/* Sessions Holding Locks and Sessions Waiting
# Purpose : This script gives the details on locks held in the database and who are
# waiting for the locks to be released .
# Input :
# Output : waiting_session, holding_session, lock_type,ctime, object_name,
# process
# */
col object_name for a30
select w.waiting_session, w.holding_session, w.lock_type, l.ctime, ob.object_name, lo.process F_PROCESS
from dba_waiters w, v$locked_object lo, v$lock l, dba_objects ob
where w.waiting_session in (select sid from v$lock where l.sid = w.waiting_session)
and w.holding_session in (select session_id from v$locked_object where session_id = w.holding_session)
and lo.object_id = ob.object_id
/
/* Program Holding the Lock and Waiting Program
#
# Purpose : This script gives the details on locks held in the database and who are
# waiting for the locks to be released .
# Input :
# Output : sid,program,ctime,type,lmode,request
# */
set pages 999 linesize 120
col request for 999999 heading “LCK REQ”
col lmode for 999999 heading “LCK HLD”
col waiting_session for 99999 heading “WAIT SID”
col holding_session for 99999 heading “HOLD SID”
select /*+ORDERED */
w.sid, substr(wp.program,1,20) waiting_program, h.sid, substr(hp.program,1,20) holding_program, h.ctime, h.type, h.lmode, w.request
from v$lock h, v$lock w,v$session wp,v$session hp
where h.id1 = w.id1
and h.id2 = w.id2
and h.block != 0
and w.request != 0
and h.lmode != 0
and w.sid = wp.sid
and h.sid = hp.sid
order by ctime
/
/* Query v$lock table
#
# Purpose : This script gives the details on locks held in the database .
# Input :
# Output : addr, kaddr, sid, type, id1, id2, lmode, request, ctime, block
# */
select * from v$lock where request != 0
order by ctime
/
/* List User Indexes
#
# Purpose : This script gives the owner, index name which are not owned by
# SYS and SYSTEM
# Input :
# Output : owner,index_name
# */
select d.owner, d.index_name
from dba_indexes d
where (d.owner, d.index_name) not in (select b.owner, b.name from bapat.perm_index_stat b)
and d.owner not in (‘SYS’, ‘SYSTEM’)
/
/* show free memory in SGA
#
# Purpose : This script gives the free memory available in the oracle
# system global area .
# Input :
# Output : free memory,bytes
# */
select name,bytes from v$sgastat
where name = ‘free memory’
/
/* Query V$License Table
#
# Purpose : This script gives the sessions_max,sessions_warning,sessions_current,
# sessions_highwater,users_max for the current database .
# Input :
# Output : sessions_max,sessions_warning,sessions_current, sessions_highwater,
# users_max
# */
select * from v$license
/
/* No. of Log Switches Per Hour during a Range of Dates
# Purpose : This script gives the details the archive logs for a given
# range of dates.
# Input : start date ,end date
# Output : count,first time
# */
break on report
compute sum of cnt on report
select count(*) cnt,to_char(first_time, ‘dd-mon-rr hh24′)
from v$loghist
where first_time between to_date(‘&strt_dt_ddmonrr_hh24mi’,'dd-mon-rr hh24:mi’)
and to_date(‘&end_dt_ddmonrr_hh24mi’,'dd-mon-rr hh24:mi’)
group by to_char(first_time, ‘dd-mon-rr hh24′)
/
clear breaks
clear comput
/* Get Process Info for Apps User
# Purpose : This script finds the oracle database process details for
# the corresponding application user.
# Input : apps user
# Output : oracle user name, session id,program,process id and machine
# */
col APP_NAME format a10
col SID format 99999
col PROGRAM format a30
col process format a6
col spid format a6
col machine format a10
undefine apps_user
select
USER_NAME APP_NAME,
c.sid SID,
c.PROGRAM PROGRAM,
c.process PROCESS,
b.spid SPID,
c.machine Machine
from apps.FND_SIGNON_AUDIT_VIEW a,v$process b, v$session c
where a.pid = b.pid and
b.addr = c.paddr and
user_name = ‘&&apps_user’
order by sid
/
/* All Apps. Users Currently Logged On
#
# Purpose : This script finds the details of the application user currently
# signed into the Oracle Applications .
# Input :
# Output : oracle user name, apps name,session id,program,process id and machine
# */
col APP_NAME format a10
col SID format 99999
col PROGRAM format a30
col process format a6
col spid format a6
col machine format a10
select
USER_NAME APP_NAME,
c.sid SID,
c.PROGRAM PROGRAM,
c.process PROCESS,
b.spid SPID,
c.machine Machine
from apps.FND_SIGNON_AUDIT_VIEW a,v$process b, v$session c
where a.pid = b.pid and
b.addr = c.paddr
order by sid
/
/* List of Concurrent Programs in a Request Class
# Purpose : This script gives the concurrent program name for a given
# request class name .
# Input : request_class_name
# Output : concurrent program name
# */
undefine p_name
column USER_CONCURRENT_PROGRAM_NAME format a40
select a.USER_CONCURRENT_PROGRAM_NAME
from fnd_concurrent_programs_tl a, fnd_concurrent_programs b
where (b.CLASS_APPLICATION_ID, b.CONCURRENT_CLASS_ID) =
( select application_id, request_class_id
from fnd_concurrent_request_class
where request_class_name = ‘&p_name’)
and a.application_id = b.application_id
and a.concurrent_program_id = b.concurrent_program_id;
/* Run Time Details For Completed Concurrent Programs
# Purpose : This script finds concurent program and run time details for the
# requests with status code is normal and warning .
# Input :
# Output : concurrent program name,program ,count,run time,average,max,min,wait,avg wait
# */
col “Short Name” format a15
col “Program” format a30
select p.concurrent_program_name “Short Name”,
p.user_concurrent_program_name “Program”,
count(*) “Cnt”,
sum(actual_completion_date – actual_start_date ) * 24 “Run Time”,
avg(actual_completion_date – actual_start_date ) * 24 “Average”,
max(actual_completion_date – actual_start_date ) * 24 “Max”,
min(actual_completion_date – actual_start_date ) * 24 “Min”,
sum(actual_start_date – requested_start_date ) * 24 “Wait”,
avg(actual_start_date – requested_start_date ) * 24 “Avg Wait”
from fnd_concurrent_programs_tl p,
fnd_concurrent_requests r
where r.program_application_id = p.application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.status_code in (‘C’,'G’)
group by p.concurrent_program_name, p.user_concurrent_program_name
order by 6
/*
#
# Purpose : This script gives the concurrent program execution summary
# by program which can be used for tuning the concurrent programs.
# Input : edate
# Output : concurrent program name,description,count,completion date
# */
set verify off
accept edate prompt ‘Enter start date: ‘
spool /tmp/cm_tune.log
set pagcsizc 59
set linesize 180
scl ncwpagc O
set recsep off
ttitlc “Concurrent Manager Program/Report Execution Summary by ProgramlAll Times are Elapsed Time rdered by Max Hours”;
CO1waited format 999999.99 hca ‘WAITEDIHOURS’;
CO1avewait format 9999.99 hca ‘AVG. IHOURS’;
CO1elapsed format 999999.99 hea ‘TOTALIHOURS’;
CO1average format 9999.99 hea ‘AVG. IHOURS’;
COImax format 9999.99 hca ‘MAX IHOURS’;
CO1min format 9999.99 hea ‘MINIHOURS’;
col concurrent~rogram_name format a20 hca
‘PROGRAM’;
CO1description format a80 wrap word;
CO1cnt format 99,999,999 hea’# TIMES IRUN’;
select p. concurrent_program_name,
p.uscr_concurrcntyogram_namc description,
count(*) cnt, sum(actual_complction_date -actual_
start_datc) * 24 elapsed,
a~,g(actual completion &[e – actual start date) * 24 -
average, m~x(actual_co-mplction_dat= -actual_
start_date) * 24 max,
min(actual completion date – actual stafi_date) * 24
min,sum(a=tual start d~te – requeste~ start date) * 24
waited, avg(actu~l_sta-ti_daie – request~d_sta-fi_date) *
24 avcwait
from fnd_concurrcnt~ rograms p,
fnd_concurrent_rcquests r
where r.program_application_id = p,application_id
and r.concurrent_program_id = p.concurrent~rogram_id
and r.status_codc in (‘C’,'G’)
and actual_completion_date > ‘&edate’
group by p, concurrcnt~rogram_name,
p.uscr_concurrentflrogram_namc
order by max desc ;
/* Queue Statistics For Concurrent Programs
#
# Purpose : This script gives the concurrent programe queue statistics.
# Input :
# Output : count ,start date,concurrent_queue_name
# */
select count(*), to_char(r.actual_start_date,’dd-mon-rr HH24:MI’), q.concurrent_queue_name
from apps.fnd_concurrent_requests r, apps.fnd_concurrent_processes p, apps.fnd_concurrent_queues q
where r.phase_code = ‘C’
and r.controlling_manager = p.concurrent_process_id
and p.concurrent_queue_id = q.concurrent_queue_id
group by to_char(r.actual_start_date, ‘dd-mon-rr HH24:MI’),q.concurrent_queue_name
/
/* Get Concurrent Program Id for a Concurrent Program
#
# Purpose : This script gives the concurrent program id for a given concurrent
# program .
# Input : user_concurrent_program_name
# Output : concurrent_program_id
# */
select concurrent_program_id from apps.fnd_concurrent_programs_tl
where user_concurrent_program_name = ‘&1′
/
/* Concurrent Program Completion Details For A Concurrent Program
#
# Purpose : This script gives the user concurrent program name , request id , start
# time , completion time ,phase code & status code for a given concurrent
# program .
# Input : concurrent program name
# Output : user concurrent program name ,request id,start date,
completion date,phase code,status code
# */
select user_concurrent_program_name, request_id, actual_start_date, actual_completion_date, phase_code, status_code
from apps.fnd_conc_requests_form_v
where user_concurrent_program_name like ‘%&prgnm%’
order by user_concurrent_program_name, request_id
/
/* Get Concurrent Request Session Details From Request Id
#
# Purpose : This script list the concurrent request phase code ,status code,actual
# start date,oracle process id , oracle session id for a given concurrent
# request id .
# Input : request id
# Output : phase code,status code,start date,process id,session id
# */
select phase_code, status_code, actual_start_date, oracle_process_id, oracle_session_id
from apps.fnd_concurrent_requests
where request_id = &requestid
/
/* Get RunTime Details for a Concurrent Program for the day
#
# Purpose : This script gives the completed concurrent requests for a single day.
# Input : concurrent program name
# Output : concurrent program name average completion date ,
# miniumum completion date ,maxiumum completion date
# */
column concurrent_program_name format a30
column user_concurrent_program_name format a30
select
fcp.concurrent_program_name,fcp.user_concurrent_program_name, count(*),
round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) AVG,
round(min((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) MIN,
round(max((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) MAX
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_vl fcp
where phase_code = ‘C’
and actual_start_date < sysdate
and actual_start_date >= sysdate – 1
and actual_completion_date < sysdate
and actual_completion_date >= sysdate – 1
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.user_concurrent_program_name like ‘%&crequest%’
group by fcp.concurrent_program_name,fcp.user_concurrent_program_name
/
/* Get Oracle Process Id, Status Code From Request Id
# Purpose : This script list the concurrent request id , argument text , phase code,
# oracle process id for a given concurrent request id .
# Input : request id
# Output : request id ,argument_text, phase_code, status_code, oracle_process_id
# */
col argument_text for a240
col oracle_process_id for a7
select request_id, argument_text, phase_code, status_code, oracle_process_id
from apps.fnd_concurrent_requests
where request_id = &reqid
/
/* List of Concurrent Programs Run For A Period
#
# Purpose : This script list the user concurrent program name,actual start date,
# actual completion date for the given range of dates .
# Input :begin date , end date
# Output :user_concurrent_program_name,actual_start_date ,actual_completion_date
# */
col user_concurrent_program_name for a40
select user_concurrent_program_name,actual_start_date ,actual_completion_date
from apps.fnd_conc_requests_form_v
where actual_completion_date >= to_date(‘&beg_dt_ddmonrr_hh24mi’, ‘DD-MON-RR HH24:MI’)
and actual_start_date <= to_date(‘&end_dt_ddmonrr_hh24mi’, ‘DD-MON-RR HH24:MI’)
order by actual_start_date
/
/* Get All Details For a Specific Request Id
# Purpose:This script list the row_id request_id , phase_code , status_code ,
# priority_request_id,
# priority ,request_date ,requested_by,requested_start_date ,
# hold_flag ,enforce_seriality_flag, single_thread_flag ,
# has_sub_request ,is_sub_request ,last_updated_by, last_update_login,
# printer ,print_style ,print_group ,request_class_application_id,
# concurrent_request_class_id, parent_request_id ,conc_login_id,
# language_id, description, req_information , resubmit_interval,
# resubmit_interval_unit_code , resubmit_interval_type_code ,
# resubmit_time , resubmit_end_date ,argument11 ,argument12 ,
# argument13 ,argument14, argument15, argument16 , argument17,
# argument18 , argument19 , argument20, argument21 , argument22,
# argument23 , argument24, enable_trace, user_cd_name ,
# program_short_name , execution_method_code, fcp_queue_method_code ,
# queue_control_flag,run_alone,sch_owner_req_id , sch_exists,
# sch_perm for a given concurrent request id .
# Input : request id
# Output :row_id request_id , phase_code , status_code , priority_request_id,
# priority ,request_date ,requested_by,requested_start_date ,
# hold_flag ,enforce_seriality_flag, single_thread_flag ,
# has_sub_request ,is_sub_request ,last_updated_by, last_update_login,
# printer ,print_style ,print_group ,request_class_application_id,
# concurrent_request_class_id, parent_request_id ,conc_login_id,
# language_id, description, req_information , resubmit_interval,
# resubmit_interval_unit_code , resubmit_interval_type_code ,
# resubmit_time , resubmit_end_date ,argument11 ,argument12 ,
# argument13 ,argument14, argument15, argument16 , argument17,
# argument18 , argument19 , argument20, argument21 , argument22,
# argument23 , argument24, enable_trace, user_cd_name ,
# program_short_name , execution_method_code, fcp_queue_method_code ,
# queue_control_flag,run_alone,sch_owner_req_id , sch_exists,
# sch_perm
# */
set feedb off
alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’
/
set feedb on
set linesize 120 pages 999
select * from apps.fnd_conc_requests_form_v where request_id = &req_id
/
/* Get Run Time Details for a Concurrent Program for a Period
#
# Purpose : This script list the concurrent request id , request date , start date,
# completion date for given user concurrent program name and request date
# between the range of dates .
# Input : start date , end date
# Output : request_id, request_date, actual_start_date, actual_completion_date
# */
select request_id, request_date, actual_start_date, actual_completion_date
from apps.fnd_conc_requests_form_v
where user_concurrent_program_name = ‘&ProgName’
and (request_date between to_date(‘&strt_dt’,'dd-mon-rr hh24:mi:ss’)
and to_date(‘&end_dt’,'dd-mon-rr hh24:mi:ss’))
order by 2
/
/* List the concurrent program details which has status code
# Purpose : This script list the concurrent program details which has status code
# and phase code is running .It also does not have pid associated with it.
# Input :
# Output : request id,concurrent program name,run time ,program id,
# phase coded,status code
# */
set feed off
select ‘Concurrent Request without PID’ CONC_REQ_WITHOUT_PID from dual
/
set feed on
select request_id, user_concurrent_program_name, (sysdate – actual_start_date)*24*60 run_time, p.concurrent_program_id PROG_ID, r.phase_code, r.status_code
from apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_tl p
where (phase_code = ‘R’ and status_code = ‘R’)
and program_application_id = application_id
and r.concurrent_program_id = p.concurrent_program_id
and oracle_process_id not in (select spid from v$process)
/
/* Get RBS Info & Concurrent Request Details
#
# Purpose : This script list the rollback segment currently being used .The script
# also gives the oracle session details for those processes using the
# rollback segments . In addition to this the script also gives the details of
# concurrent program which owns this processes .
# Input :
# Output :
# */
set lines 200 trimspool on verify off
column program format a25 trunc
column description format a20 trunc
column name format a10 trunc
column username format a15 trunc
column user_concurrent_program_name format a30 trunc
column urs_mb heading ‘Used’
column rs_mb heading ‘C.Size’
define blksz=0
column db_blk_sz new_value blksz
set term off
select value db_blk_sz
from v$parameter
where name = ‘db_block_size’;
select &&blksz
from dual;
set term on
spool /tmp/roll
select r.name, rssize / ( 1024 * 1024 ) rs_mb,
sum(used_ublk) * &&blksz / ( 1024 * 1024 ) urs_mb
from v$transaction t, v$rollname r, v$rollstat s
where t.xidusn = r.usn
and r.usn = s.usn
group by r.name , rssize;
break on name skip 1 on sid on serial on username
select r.name, username, s.sid, s.serial#,
used_ublk * &&blksz / ( 1024 * 1024 ) used,
object_name, start_time
from v$transaction t, v$rollname r,
v$session s, v$locked_object l,
all_objects o
where t.xidusn = r.usn
and s.taddr=t.addr
and l.session_id = s.sid
and l.xidusn = t.xidusn
and l.xidslot = t.xidslot
and l.xidsqn = t.xidsqn
and l.object_id = o.object_id
order by 1,2,3,4,5;
select f.request_id, s.sid, s.program, u.description , r.name,
c.user_concurrent_program_name
from v$session s, v$transaction t, v$rollname r,
v$process p, apps.fnd_concurrent_requests f, apps.fnd_user u,
apps.fnd_concurrent_programs_vl c
where f.oracle_process_id = p.spid
and p.addr=s.paddr
and s.taddr=t.addr
and t.xidusn = r.usn
and f.requested_by = u.user_id
and f.program_application_id = c.application_id
and f.concurrent_program_id = c.concurrent_program_id
and f.status_code = ‘R’
and f.phase_code = ‘R’;
spool off
/* Get RunTime Statistics for A Concurrent Program (Phase Code Completed)
#
# Purpose : This script list the concurrent program details for phase code as
# completed for a given concurrent program .
# Input : concurrent program name
# Output : concurrent program name,average completion date,miniumum completion
# date,maxiumum completion date
# */
column concurrent_program_name format a30
column user_concurrent_program_name format a30
alter session set nls_date_format=’DD-MON-RR HH24:MI:SS’
/
select
fcp.concurrent_program_name,fcp.user_concurrent_program_name, count(*),
round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) AVG,
round(min((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) MIN,
round(max((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) MAX
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_vl fcp
where phase_code = ‘C’
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.user_concurrent_program_name like ‘%&prgname%’
group by fcp.concurrent_program_name,fcp.user_concurrent_program_name
/
/* Get Process and Session Details for a Concurrent Request Id
#
# Purpose : This script list the concurrent request id,process id,process ,serial#,
# description for a given concurrent request id .
# Input : request id
# Output : request id,pid,sid,serial#,process,program,description
# */
select f.request_id,p.spid B_Process,s.sid,s.serial#,s.process F_Process,s.program, u.description
from v$session s, v$process p, apps.fnd_concurrent_requests f, apps.fnd_user u
where f.oracle_process_id = p.spid
and p.addr=s.paddr
and f.request_id=&reqid
and f.requested_by = u.user_id
/
/* Get Process and Session Id from Concurrent Request Id
#
# Purpose : This script list the concurrent request id , process id, process,
# session id
# , serial# , concurrent program name for a given concurrent request id .
# Input : request id
# Output : request id,pid,sid,serial#,process,program
# */
select f.request_id,p.spid B_Process,s.sid,s.serial#,s.process F_Process,s.program
from v$session s, v$process p, apps.fnd_concurrent_requests f
where f.oracle_process_id = p.spid
and p.addr=s.paddr
and f.request_id=&reqid
/* Get Process Id, Session Id and SQL text for a Concurrent Request
#
# Purpose : This script list the concurrent request id , session id ,process , sql
# text for a given concurrent request id .
# Input : request id
# Output : request id,pid,sid,serial#,process,sql_text
# */
select f.request_id,p.spid B_Process,s.sid,s.serial#,s.process F_Process,a.sql_text
from v$session s, v$process p, apps.fnd_concurrent_requests f, v$sqlarea a
where f.oracle_process_id = p.spid
and p.addr=s.paddr
and f.request_id=&reqid
and s.sql_address = a.address
/
/* Concurrent Program Run Time Details for a Period
# Purpose : This script list the concurrent program name , count,average ,run time
# details for given concurrent programs ,dates and phase code
# is completed .
# Input : start date,completion date,program name
# Output : concurrent program name,count,average start date,miniumum start date,
# : maxiumum start date
# */
column concurrent_program_name format a30
column user_concurrent_program_name format a30
alter session set nls_date_format=’DD-MON-RR HH24:MI:SS’
/
select
fcp.concurrent_program_name,fcp.user_concurrent_program_name, count(*),
round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) AVG,
round(min((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) MIN,
round(max((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) MAX
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_vl fcp
where phase_code = ‘C’
and actual_start_date >= to_date(‘&ST_DD_MON_RR_HH24_MI_SS’)
and actual_completion_date <= to_date(‘&CT_DD_MON_RR_HH24_MI_SS’)
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.user_concurrent_program_name like ‘%&prgname%’
group by fcp.concurrent_program_name,fcp.user_concurrent_program_name
/
/* Get the Datafile Usage for APPLSYSD tbs
# Purpose : This script list the blocks used up in a datafile which can be used to
# figure out whether this particular datafile needs to be resized for the
# tablespace APPLSYSD .
# Input :
# Output : file name ,size
# */
select file_name, round((max(e.block_id + e.blocks) * 8192 / (1024 * 1024)) + 10)
from dba_extents e, dba_data_files f
where e.tablespace_name = ‘APPLSYSD’
and e.file_id = f.file_id
group by file_name
/
/* Get the parent request and child request for a Concurrent Request
# Purpose : This script list the concurrent request id ,sub request, parent
# request id,user concurrent program name for phase code is
# running , status code is normal and oracle process id is null .
# Input :
# Output : request id,is_sub_request, has_sub_request, parent_request_id,
# concurrent program name
# */
col user_concurrent_program_name for a45
col is_sub_request heading ‘IS’
col has_sub_request heading ‘HAS’
col parent_request_id heading ‘PARENT’
select request_id, is_sub_request, has_sub_request, parent_request_id, user_concurrent_program_name from apps.fnd_conc_requests_form_v
where phase_code= ‘R’ and status_code = ‘R’
and oracle_process_id is Null
/
/* Get the Concurrent Program currently running and Pending
# Purpose : This script list the concurrent request id ,user conncurrent program name
# for phase code is running & pending , status code is normal
# and the request is not currently on hold .
# Input :
# Output : request_id, user_concurrent_program_name, phase_code, status_code
# */
col request_id format 9999999
col Running format a50
select request_id, user_concurrent_program_name “Running”, phase_code, status_code
from apps.fnd_conc_requests_form_v c
where (c.phase_code = ‘R’ or (c.phase_code=’P’ and c.status_code=’I'))
and c.concurrent_program_id in
(select distinct s.running_concurrent_program_id
from apps.fnd_conc_requests_form_v c, apps.fnd_concurrent_program_serial s
where c.concurrent_program_id = s.to_run_concurrent_program_id
and phase_code = ‘P’
and hold_flag != ‘Y’
and requested_start_date <= sysdate)
/
/* Concurrent Programs Currently Running
#
# Purpose : This script list the concurrent request id , program name,phase code ,
# status code for a given request id , phase code is running and
# status code normal .
# Input : request id
# Output : request_id, user_concurrent_program_name, phase_code, status_code
# */
col request_id format 999999999
col Running format a50
select request_id, user_concurrent_program_name “Running”, phase_code, status_code
from apps.fnd_conc_requests_form_v c
where (c.phase_code = ‘R’ or (c.phase_code=’P’ and c.status_code = ‘I’))
and c.concurrent_program_id in
(select s.running_concurrent_program_id
from apps.fnd_conc_requests_form_v c, apps.fnd_concurrent_program_serial s
where request_id = &request_id
and c.concurrent_program_id = s.to_run_concurrent_program_id)
/
/* RunTime Statistics for Concurrent Programs running between low and High Time Limits
#
#
# Purpose : This script gives the concurrent program name,count,average
# completion date,miniumum completion date,maxiumum completion date for
# phase code as complete and status code as completed,normal,running and
# time between the range given .
# Input : high time in minutes , low time in minutes
# Output : concurrent program name,count,average completion date,miniumum
# completion date,maxiumum completion date
# */
set lines 79
set pages 50
column concurrent_program_name format a10
column concurrent_program_name heading Program
column user_concurrent_program_name format a28
column user_concurrent_program_name heading “Program Name”
column count(*) heading “#Runs”
column “#Runs” format 99999
column round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) heading Average format 9999.99
column round(min((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) heading Min format 9999.99
column round(max((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) heading Max format 9999.99
select
fcp.concurrent_program_name,
fcp.user_concurrent_program_name,
count(*),
round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2),
round(min((fcr.actual_completion_date-fcr.actual_start_date)*1440),2),
round(max((fcr.actual_completion_date-fcr.actual_start_date)*1440),2)
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_vl fcp
where phase_code = ‘C’
and status_code in ( ‘C’,'I’,'R’)
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
group by fcp.concurrent_program_name,fcp.user_concurrent_program_name
having round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) >=&Low_Time_in_minutes
and round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) < &High_Time_in_minutes
order by 4 desc
/
/* RunTime Details of Concurrent programs That ran during a Period
#
#
# Purpose : This script gives the concurrent program name,count,average
# completion date,miniumum completion date,maxiumum completion date for
# phase code as complete and status code as completed,normal,running and
# date between the range given .
# Input : start date ,end date
# Output : concurrent program name,count,average completion date,miniumum
# completion date,maxiumum completion date
# */
set lines 100
set pages 50
column concurrent_program_name format a15
column concurrent_program_name heading Program
column user_concurrent_program_name format a35
column user_concurrent_program_name heading “Program Name”
column count(*) heading “#Runs”
column “#Runs” format 99999
column round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) heading Average format 9999.99
column round(min((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) heading Min format 9999.99
column round(max((fcr.actual_completion_date-fcr.actual_start_date)*1440),2) heading Max format 9999.99
select
fcp.concurrent_program_name,
fcp.user_concurrent_program_name,
count(*),
round(avg((fcr.actual_completion_date-fcr.actual_start_date)*1440),2),
round(min((fcr.actual_completion_date-fcr.actual_start_date)*1440),2),
round(max((fcr.actual_completion_date-fcr.actual_start_date)*1440),2)
from apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_vl fcp
where phase_code = ‘C’
and fcr.program_application_id = fcp.application_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.actual_completion_date >= to_date(‘&beg_dt_ddmonrr_hh24mi’, ‘DD-MON-RR HH24:MI’)
and fcr.actual_start_date <= to_date(‘&end_dt_ddmonrr_hh24mi’, ‘DD-MON-RR HH24:MI’)
group by fcp.concurrent_program_name,fcp.user_concurrent_program_name
order by 4 desc
/
/* concurrent program name for start
# Purpose : This script gives the start date , concurrent program name for start
# date & completion date between ’27-MAR-00 19:00′ and ’28-JAN-00 00:59′ .
# Input :
# Output : start date,concurrent program name,count
# */
break on Hour skip 1
col user_concurrent_program_name for a40
select to_char(actual_start_date, ‘DD-MON-RR HH’) Hour ,user_concurrent_program_name, count(*)
from apps.fnd_conc_requests_form_v
where
(to_date(’27-MAR-00 19:00′,’DD-MON-RR HH24:MI’) between
actual_start_date and actual_completion_date)
or
(to_date(’28-JAN-00 00:59′,’DD-MON-RR HH24:MI’) between
actual_start_date and actual_completion_date)
group by to_char(actual_start_date, ‘DD-MON-RR HH’),user_concurrent_program_name
/
/* Conc.programs that ran during a given range of dates
# Purpose : This script gives the start date , concurrent program and total count
# for a given range of dates .
# Input : start date,completion date
# Output : start date,concurrent program,total
# */
col user_concurrent_program_name for a40
break on Hour skip 1
compute sum of Total on Hour
select to_char(actual_start_date, ‘DD-MON-RR HH24′) Hour ,user_concurrent_program_name, count(*) Total
from apps.fnd_conc_requests_form_v
where actual_completion_date >= to_date(‘&strt_dt_ddmonrr_hh24mi’, ‘DD-MON-RR HH24:MI’)
and actual_start_date <= to_date(‘&cmpl_dt_ddmonrr_hh24mi’, ‘DD-MON-RR HH24:MI’)
group by to_char(actual_start_date, ‘DD-MON-RR HH24′),user_concurrent_program_name
/
/* Create Scripts For Killing bpa sessions Originating from sclpdb09
#
# Purpose : This script creates the unix script f_kill_bpa.sh which contains
# oracle sql statements for killing sessions coming from sclpdb09 .
# Input :
# Output : alter system kill session
# */
set heading off
set verify off
set feedback off
spool /tmp/f_kill_bpa.sh
select ‘alter system kill session ”’||sid||’,'||serial#||”’;’
from v$session where machine=’sclpdb09′
/
spool off
/* Get Req.Id,Phase Code and Status Code for Oracle Processes Not Running
# Purpose : This script gives the concurrent request id ,phase code , status code
# oracle process id for those oracle processes currently not running .
# Input :
# Output : request id,phase code, status code, oracle process id
# */
select request_id, phase_code, status_code, oracle_process_id
from apps.fnd_concurrent_requests
where oracle_process_id not in (select spid from v$process)
/
/* Objects in XAMD tbs owned by non XXAM_% users
# Purpose : This script gives the owner,database object name , object type ,
# last ddl time , size in MB for all the non XXAM table & indexes
# in the tablespace XAMD .
# Input :
# Output : owner,object_name,object_type,last_ddl_time,size
# */
select dba_objects.owner, object_name, object_type ,last_ddl_time ,bytes/1024/1024
from dba_objects, dba_segments
where dba_objects.owner = dba_segments.owner
and tablespace_name = ‘XAMD’
and dba_objects.owner not like ‘XXAM%’
and object_type in (‘TABLE’,'INDEX’)
and object_type = segment_type
and object_name = segment_name
order by dba_objects.owner, last_ddl_time
/
/* Get the Parent Request id for a Conc.Request Id
# Purpose : This script gives the miniumum concurrent request id for a given
# request id .
# Input : request id
# Output : miniumum request id
# */
select min(request_id) from apps.fnd_concurrent_requests
connect by prior parent_request_id= request_id
start with request_id=&req;
/* Apps Userid, name, description and star/end time for a BG Process
# Purpose : This script gives the apps user id , user name , description ,
# start time , end time for a given oracle process .
# Input : pid
# Output : user_id, user_name, description, start_time, end_time
# */
select fl.user_id, fu.user_name, fu.description, fl.start_time, fl.end_time
from v$process p, apps.fnd_logins fl, apps.fnd_user fu
where p.pid = fl.pid
and fl.user_id = fu.user_id
and fl.end_time is Null
and p.pid = &pid
/
/* List of Conc Request Details for a Conc Program.
# Purpose : This script gives the concurrent program name , request id ,
# requested start date ,phase code,status code for phase code is
# pending ,status code is standby and currently request is
# not in hold .
# Input :
# Output : user_concurrent_program_name, request_id, requested_start_date,
# phase_code, status_code
# */
col user_concurrent_program_name for a40
break on user_concurrent_program_name
select user_concurrent_program_name, request_id, requested_start_date, phase_code, status_code
from apps.fnd_conc_requests_form_v
where phase_code = ‘P’
and status_code = ‘Q’
and hold_flag != ‘Y’
and requested_start_date <= sysdate
order by user_concurrent_program_name, request_id
/
break on request_id
/* Get the parent request Id,status code and phase code for a Conc. Request Id.
#
# Purpose : This script gives the concurrent program request id ,sub request ,
# parent request ,status code ,phase code for a given request id .
# Input : request id
# Output : request id, is_sub_request, parent_request_id,has_sub_request,
# status_code, phase_code
# */
select r1.request_id, r1.is_sub_request, r1.parent_request_id, r1.has_sub_request,
r1.status_code, r1.phase_code
from apps.fnd_concurrent_requests r1
where r1.parent_request_id = ( select r2.request_id
from apps.fnd_concurrent_requests r2
where r2.request_id = &reqid)
/
/* Get Conc.Program Name,phase code and start/end time for a Conc. Request
#
# Purpose : This script gives the concurrent program name,request id ,phase,status ,
# start_time,end_time for a given concurrent request id .
# Input : request id
# Output : user_concurrent_program_name,request id,phase,status,start_Time,
# end_Time
# */
set lines 120
select substr(l_pad||user_concurrent_program_name,1,40) Program,Request_Id,
Phase,Status,Start_Time,End_Time
from ( select substr(lpad(‘ ‘,2*(level-1)),1,20) l_pad,
request_id,concurrent_program_id program_id,
decode(status_code,’I',’Normal’,'C’,'Normal’,'R’,'Normal’,'H’,'On Hold’,'W’,'Paused’,'B’,'Resuming’,'P’,'Scheduled’,'M’,'Migrating’,'Q’,'Standby’,'G’,'Warning’,'K’,'Terminated’,'T’,'Terminating’,'A’,'Paused’,'Z’,'Paused’,'D’,'Cancelled’,'E’,'Error’) status,
decode(phase_code,’R',’Running’,'P’,'Pending’,'C’,'Completed’,'I’,'Inactive’) phase,
to_char(actual_start_date,’dd-mon hh24:mi:ss’) start_time,
to_char(actual_completion_date,’dd-mon hh24:mi:ss’) end_time
from apps.fnd_concurrent_requests
connect by prior request_id=parent_request_id
start with request_id=&req_id
), apps.fnd_concurrent_programs_tl
where program_id=concurrent_program_id
order by request_id;
/* Get Conc.Program Name,phase code and start/end time for a Conc. Request
#
# Purpose : This script gives the concurrent program name,request id ,phase,status ,
# start_time,end_time for a given concurrent request id .
# Input : request id
# Output : user_concurrent_program_name,request id,phase,status,start_Time,
# end_Time
# */
set lines 120
select substr(l_pad||user_concurrent_program_name,1,40) Program,Request_Id,
Phase,Status,Start_Time,End_Time
from ( select substr(lpad(‘ ‘,2*(level-1)),1,20) l_pad,
request_id,concurrent_program_id program_id,
decode(status_code,’I',’Normal’,'C’,'Normal’,'R’,'Normal’,'H’,'On Hold’,'W’,'Paused’,'B’,'Resuming’,'P’,'Scheduled’,'M’,'Migrating’,'Q’,'Standby’,'G’,'Warning’,'K’,'Terminated’,'T’,'Terminating’,'A’,'Paused’,'Z’,'Paused’,'D’,'Cancelled’,'E’,'Error’) status,
decode(phase_code,’R',’Running’,'P’,'Pending’,'C’,'Completed’,'I’,'Inactive’) phase,
to_char(actual_start_date,’dd-mon hh24:mi:ss’) start_time,
to_char(actual_completion_date,’dd-mon hh24:mi:ss’) end_time
from apps.fnd_concurrent_requests
connect by prior request_id=parent_request_id
start with request_id=&req_id
), apps.fnd_concurrent_programs_tl
where program_id=concurrent_program_id
order by request_id;
/* Conc.Program Completion details for a Concurrent Program.
#
# Purpose : This script gives the concurrent program name , phase code , status
# code , actual start date , actual completion date for a given
# concurrent program name and status code is normal .
# Input : user_concurrent_program_name
# Output : user_concurrent_program_name,conc,phase_code,status_code,
# actual_start_date,actual_completion_date
# */
select rtrim(p.user_concurrent_program_name) conc,phase_code,status_code,actual_start_date,actual_completion_date
from apps.fnd_concurrent_requests r,apps.fnd_concurrent_programs_tl p
where p.concurrent_program_id=r.concurrent_program_id and
user_concurrent_program_name=’&prg’
and status_code<>’C’
order by 4 desc
/
/* Req Id, start/end date and completion time in Minutes for a Conc.Program
# Purpose : This script gives the concurrent request id , actual start date ,
# actual completion date , time difference in minutes for a
# given concurrent program .
# Input : user_concurrent_program_name
# Output : request id,actual_start_date,actual_completion_date
# */
select request_id,actual_start_date,actual_completion_date,
(actual_completion_date-actual_start_date)*1400 Minutes
from
fnd_concurrent_requests r,fnd_concurrent_programs_vl p
where
r.concurrent_program_id=p.concurrent_program_id
and user_concurrent_program_name=’&prg_name’
and r.program_application_id=p.application_id
order by 2
/
/* Get Request id, sid and serial# from the background Process
# Purpose : This script gives the concurrent request id , oracle process id ,
# oracle session id , serial# ,process ,program for a given
# oracle process .
# Input : spid
# Output : request_id,spid ,sid,serial#,process ,program
# */
select f.request_id,p.spid B_Process,s.sid,s.serial#,s.process F_Process,s.program
from v$session s, v$process p, apps.fnd_concurrent_requests f
where f.oracle_process_id = p.spid
and f.actual_completion_date is Null
and p.addr=s.paddr
and p.spid=&bg_process
/
/* User Profile Changes in the Last 22 Days
# Purpose : This script gives the apps user profile option name , last updated
# date ,level id , last updated by for last updated date less than
# sysdate – 22 days .
# Input :
# Output : user_profile_option_name,last_update_date,level_id,last_updated_by
# */
select substr(user_profile_option_name,1,30), to_char(a.last_update_date,’DD-MON-YY HH24:MI:SS’),level_id,a.last_updated_by
from apps.FND_PROFILE_OPTION_VALUES a,apps.fnd_profile_options_vl b
where a.LAST_UPDATE_DATE > sysdate -22 and
a.profile_option_id = b.profile_option_id and
a.application_id = b.application_id
order by 2
/
/* Create sql for Updating fnd_profile_option_values
# Purpose : This script gives the update sql statement for updating
# apps.fnd_profile_option_values for profile option value
# like ‘%sclpdb04%’ .
# Input :
# Output : fnd_profile_option_values
# */
select ‘update apps.fnd_profile_option_values’,
‘set profile_option_value = ”’ ||profile_option_value||”” c2,
‘ where application_id = ‘||application_id||’ and profile_option_id = ‘||profile_option_id||
‘ and level_id = ‘||level_id||’ and ‘ c3,’profile_option_value = ”’||profile_option_value||”’;’
from apps.fnd_profile_option_values
where PROFILE_OPTION_VALUE like ‘%sclpdb04%’
/
/* Get Profile Option Value from option name
# Purpose : This script gives the profile option value , profile option name
# for a given profile option name .
# Input :
# Output : profile_option_value,profile_option_name
# */
select a.profile_option_value,b.profile_option_name from apps.fnd_profile_options b,apps.fnd_profile_option_values a
where a.profile_option_id=b.profile_option_id and b.profile_option_name like ‘%&1%’
/
/* Create scripts to retain profile Option Values for Refresh
# Purpose : This script extract the profile options like Site-name, OEOEPC, RRA
# before refresh and use the generated f_prof_source.lst
# to update the profile options in the refreshed environment .
# Input :
# Output : update sql statement
# */
REM Script to extract the profile options like Site-name, OEOEPC, RRA
REM before refresh and use the generated f_prof_source.lst
REM to update the profile options in the refreshed environment
REM
REM Dt. : 07/26/99
REM
set pages 0
set feed off
set echo off
set lines 240
spool /tmp/f_prof_refresh
select ‘update fnd_profile_option_values set profile_option_value = ‘||””||
a.PROFILE_OPTION_VALUE||””||
‘ where profile_option_id = (select profile_option_id from fnd_profile_options where profile_option_name=’||””||b.profile_option_name||””||’);’
from fnd_profile_options b,fnd_profile_option_values a
where
a.profile_option_id=b.profile_option_id and
b.profile_option_name in (‘APPS_WEB_AGENT’,'HELP_BASE_URL’,
‘FILE_SERVER_URL’,
‘TCF:HOST’,'TCF:PORT’,'XXAM_ENG_PDM_URL_LOCATION’,
‘SITENAME’,'OE_APPLICATION_SERVER’,'FS_SVC_PREFIX’,
‘ECE_OUT_FILE_PATH’,'ECE_IN_FILE_PATH’,'ATTACHMENT_FILE_DIRECTORY’)
/
select ‘exit;’ from dual
/
exit
/
spool off
/* User Profile Option name and Value for level != 10004
# Purpose : This script gives the apps user profile option name ,
# last updated date , description for level id not in 10004 .
# Input :
# Output : user_profile_option_name, profile_option_value, last_update_date,
# description
# */
alter session set nls_date_format=’dd-mon-rr hh24:mi’
/
col user_profile_option_name for a35
col profile_option_value for a20
col user for a25
set lines 120
select fpot.USER_PROFILE_OPTION_NAME, fpov.PROFILE_OPTION_VALUE, fpov.LAST_UPDATE_DATE, fu.DESCRIPTION “USER”
from apps.fnd_profile_options fpo, apps.fnd_profile_options_tl fpot, apps.fnd_profile_option_values fpov,
apps.fnd_user fu
where fpov.last_update_date >= sysdate – 30
and fpov.application_id = fpo.application_id
and fpov.profile_option_id = fpo.profile_option_id
and fpo.profile_option_name = fpot.profile_option_name
and fpov.last_updated_by = fu.user_id
and fpov.level_id <> 10004
/
/* Conc Program Run Time Details during a Period
# Purpose : This script gives the concurrent program name , request id ,
# actual start date, actual completion date , requested start date ,
# phase code , status code for given range of dates and status code
# as normal , cancelled & error .
# Input : start date ,completion date
# Output : user_concurrent_program_name, request_id, actual_start_date,
# actual_completion_date, requested_start_date, phase_code, status_code
# */
col user_concurrent_program_name for a40
select user_concurrent_program_name, request_id, actual_start_date, actual_completion_date, requested_start_date,
phase_code, status_code
from apps.fnd_conc_requests_form_v
where user_concurrent_program_name like ‘%&userprgnm%’
and actual_start_date >= to_date(‘&startdt_dd_mon_rr_hh24_mi’,'dd-mon-rr hh24:mi’)
and ((actual_completion_date <= to_date(‘&cmplndt_dd_mon_rr_hh24_mi’,'dd-mon-rr hh24:mi’)
and status_code = ‘C’)
or status_code in (‘D’, ‘E’))
/
/* Request Id, Apps User and parameters submitted for AMAT BPA Transfer Order Label
# Purpose : This script gives the concurrent program name , request id ,
# description ,arguments for phase code as running , status
# code as normal and concurrent program name is
# ‘AMAT BPA Transfer Order Label’ .
# Input :
# Output : user_concurrent_program_name,request_id,description,
# argument1, argument7,argument8, argument9,argument10
# */
col user_concurrent_program_name for a30 heading “Program Name”
col User for a20 heading “User Name”
col argument1 for a10 heading “Parameter”
col argument7 for a2 heading “P1″
col argument8 for a2 heading “P2″
col argument9 for a2 heading “P3″
col argument10 for a7 heading “Printer”
break on user_concurrent_program_name
select user_concurrent_program_name, request_id, substr(b.description,1,20) “User”, argument1, argument7,argument8, argument9,argument10
from apps.fnd_conc_requests_form_v a, apps.fnd_user b
where phase_code = ‘R’ and status_code = ‘R’
and user_concurrent_program_name = ‘AMAT BPA Transfer Order Label’
and user_id = requested_by
/
/* Currently Running / Pending Conc Programs
#
# Purpose : This script list the concurrent request id, program name ,start date ,
# run time,phase code , status code , concurrent program ,description for
# the concurrent requests with phase code is running or pending and
# status code is normal .
# Input :
# Output : request_id, user_concurrent_program_name,actual_start_date,
# run_time,concurrent_program_id,phase_code,status_code,description
# */
set lines 120
set pages 999
column user_concurrent_program_name for a50
column description for a15
select /*+ ORDERED */
request_id, user_concurrent_program_name,round((sysdate-actual_start_date)*(24*60),1) run_time,
p.concurrent_program_id C_PROGRAM_ID, r.phase_code, r.status_code, substr(u.description,1,15) “Username”
from applsys.fnd_concurrent_programs_tl p, applsys.fnd_concurrent_requests r,
v$process pr, apps.fnd_user u
where ((phase_code = ‘R’ and r.oracle_process_id = pr.spid )
or (phase_code = ‘P’ and status_code in (‘C’,'R’)))
and program_application_id = application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.requested_by = u.user_id
order by run_time
/
/* Session Details for Currently Running Conc Programs
# Purpose : This script list the session id , process id , program,description ,
# responsiblity name , user form name , start time for all concurrent
# programs currently running .
# Input : sid
# Output : sid,serial#,spid,process ,program,description,responsibility_name,
# user_form_name, start_time
# */
alter session set nls_date_format = ‘DD-MON-YY HH24:MI:SS’
/
column description for a20
column responsibility_name for a25
column user_form_name for a30
select s.sid, s.serial#,p.spid “B Process”, s.process “F Process”, s.program,
u.description , r.responsibility_name, f.user_form_name, rf.start_time
from v$process p, v$session s, apps.fnd_logins l, apps.fnd_user u,
apps.fnd_login_resp_forms rf, apps.fnd_form_vl f,
apps.fnd_responsibility_tl r, apps.fnd_login_responsibilities lr
where sid = &SID
and p.addr = s.paddr
and p.pid = l.pid
and s.process = l.spid
and l.user_id = u.user_id
and l.login_id = rf.login_id
and rf.form_id = f.form_id
and rf.form_appl_id = f.application_id
and l.login_id = lr.login_id
and lr.responsibility_id = r.responsibility_id
and lr.resp_appl_id = r.application_id
and rf.end_time is Null
and l.terminal_id <> ‘Concurrent’
order by start_time
/
/* Session, FG and BG Process Details for a specific SID
# Purpose : This script list the oracle session id ,process id ,process , program,
# machine ,description for a given oracle applications session id .
# Input : sid
# Output : sid, spid , process , program, machine,description
# */
select s.sid, p.spid “B Process”, s.process “F Process”, s.program, s.machine, u.description
from v$process p, v$session s, apps.fnd_logins l, apps.fnd_user u
where sid = &SID
and p.addr = s.paddr
and p.pid = l.pid
and s.process = l.spid
and l.user_id = u.user_id
/
/* Currently Running, Pending and Inactive Conc Programs
# Purpose : This script list the miniumum concurrent request id , program name ,
# phase code ,status code for phase code is running , pending , inactive and
# status code is standby with request id is not on hold .
# Input :
# Output : request_id, user_concurrent_program_name,request_id,
# phase_code, status_code
# */
col request_id format 9999999
col Running format a50
select min(c1.request_id), c1.user_concurrent_program_name “Pending”,
c2.request_id, c2.user_concurrent_program_name “Running”,
c2.phase_code, c2.status_code
from apps.fnd_conc_requests_form_v c1, apps.fnd_conc_requests_form_v c2,
apps.fnd_concurrent_program_serial s
where (c2.phase_code = ‘R’ or (c2.phase_code=’P’ and c2.status_code=’I'))
and c2.concurrent_program_id in
(select distinct s.running_concurrent_program_id
from apps.fnd_conc_requests_form_v c, apps.fnd_concurrent_program_serial s
where c.concurrent_program_id = s.to_run_concurrent_program_id
and phase_code = ‘P’
and hold_flag != ‘Y’
and requested_start_date <= sysdate)
and c1.concurrent_program_id=s.to_run_concurrent_program_id
and c2.concurrent_program_id=s.running_concurrent_program_id
and c1.phase_code=’P’ and c1.status_code=’Q’
group by c1.request_id,c1.user_concurrent_program_name,
c2.request_id,c2.user_concurrent_program_name,
c2.phase_code, c2.status_code
/
/* Appl. Username, start date and Description for Apps User
# Purpose : This script list the application user id , application user name ,
# start date , description for a given user id .
# Input : emp_id
# Output : user_id,user_name, start_date, description
# */
select user_id,user_name, start_date, description from apps.fnd_user
where user_name = upper (‘&emp_id’)
/
/* List Database Users
# Purpose : This script list the username in the database for username not like
# ‘XXAM%’ , ‘W%’ , ‘%BOARD%’ , ‘BRIO%’ , ‘BPA%’ , ‘O%’ , ‘CW%’ , ‘SYS%’ ,
# ‘PM’,'QV’,'CTXSYS’,'ENPULSE’,'DBSNMP’,'EPIPHANY’,'JOB_MONITOR’,'PATCH’ .
# Input :
# Output : username
# */
select username from dba_users
where username not in (select oracle_username from apps.fnd_oracle_userid)
and username not like ‘XXAM%’
and username not like ‘W%’
and username not like ‘%BOARD%’
and username not like ‘BRIO%’
and username not like ‘BPA%’
and username not like ‘O%’
and username not like ‘CW%’
and username not like ‘SYS%’
and username not in (‘PM’,'QV’,'CTXSYS’,'ENPULSE’,'DBSNMP’,'EPIPHANY’,'JOB_MONITOR’,'PATCH’)
order by 1
/
/* Get Username and Description for Apps. User
#
# Purpose : This script list the application user id , user name , description
# for a given user id .
# Input : userid
# Output : user_id, user_name, description
# */
select user_id, user_name, description from apps.fnd_user where user_id = &userid
/
/* Users Logged at More than 2 placess
# Purpose : This script list the applicatioin user name , process id ,
# number of sessions for number of sessions greater than four per user .
# Input :
# Output : user_name,pid,no_of_sessions
# */
col USER_NAME format a30;
col No_Of_Sessions format 9999;
ttitle center “Users logged in At more then 2 Places…”
select USER_NAME,PID,count(*) No_Of_Sessions
FROM
APPS.FND_SIGNON_AUDIT_VIEW
group BY USER_NAME ,PID
having count(*) > 4
/
/* Currently Running Conc programs and Run Time
# Purpose : This script list the concurrent request id , program name ,run time ,
# concurrent program id for phase code is running .
# Input :
# Output : request_id, user_concurrent_program_name,run_time,
# concurrent_program_id
# */
set pages 10000
set linesize 100
column user_concurrent_program_name format a55
column concurrent_program_id format 999999
select request_id, user_concurrent_program_name,(sysdate-actual_start_date)*(24*60) run_time, p.concurrent_program_id “C PGM ID”
from applsys.fnd_concurrent_requests r, applsys.fnd_concurrent_programs_tl p
where
phase_code = ‘R’
and program_application_id = application_id
and r.concurrent_program_id = p.concurrent_program_id
order by run_time
/
/* Currently Running Conc. Programs with No BG Process
# Purpose : This script list the concurrent request id , concurrent program name ,
# run time , concurrent program id , phase code , status code for
# phase code is running , status code is normal and no oracle process
# running at the unix level .
# Input :
# Output : request_id, user_concurrent_program_name,run_time,
# concurrent_program_id , phase_code, status_code
# */
set feed off
select ‘Concurrent Request with No Process ID’ Conc_Req_no_Process_ID from dual
/
set feed on
select request_id, user_concurrent_program_name, (sysdate – actual_start_date)*24*60 run_time, p.concurrent_program_id PROG_ID, r.phase_code, r.status_code
from apps.fnd_concurrent_requests r, apps.fnd_concurrent_programs_tl p
where (phase_code = ‘R’ and status_code = ‘R’)
and program_application_id = application_id
and r.concurrent_program_id = p.concurrent_program_id
and oracle_process_id = Null
/
/* Currently Running Concurrent Requests
# Purpose : This script list the concurrent request id , program name , run time ,
# concurrent program id for phase code is running .
# Input :
# Output : request_id, user_concurrent_program_name,run_time,
# concurrent_program_id
# */
set pages 10000
set linesize 100
column user_concurrent_program_name format a50
column concurrent_program_id format 999999999
select request_id, user_concurrent_program_name,(sysdate-actual_start_date)*(24*60) run_time, p.concurrent_program_id
from applsys.fnd_concurrent_requests r, applsys.fnd_concurrent_programs_tl p
where
phase_code = ‘R’
and program_application_id = application_id
and r.concurrent_program_id = p.concurrent_program_id
order by run_time
/
/* RunTime Details for currently Running Conc Programs
# Purpose : This script list the concurrent request id , program name , run time ,
# concurrent program id , phase code , status code ,username for
# phase code is running & pending and status code is normal .
# Input :
# Output : request_id, user_concurrent_program_name,run_time,
# concurrent_program_id ,phase_code, status_code,username
# */
set lines 120
set pages 999
column user_concurrent_program_name for a50
column description for a25
set timing on
select
request_id, user_concurrent_program_name,round((sysdate-actual_start_date)*(24*60),1) run_time,
p.concurrent_program_id C_PROGRAM_ID, r.phase_code, r.status_code, substr(u.description,1,15) “Username”
from applsys.fnd_concurrent_programs_tl p, applsys.fnd_concurrent_requests r,
apps.fnd_user u
where phase_code = ‘P’ and status_code in (‘C’,'R’)
and program_application_id = application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.requested_by = u.user_id
union all
select
request_id, user_concurrent_program_name,round((sysdate-actual_start_date)*(24*60),1) run_time,
p.concurrent_program_id C_PROGRAM_ID, r.phase_code, r.status_code, substr(u.description,1,15) “Username”
from applsys.fnd_concurrent_programs_tl p, applsys.fnd_concurrent_requests r,
v$process pr, apps.fnd_user u
where phase_code = ‘R’ and r.oracle_process_id = pr.spid
and program_application_id = application_id
and r.concurrent_program_id = p.concurrent_program_id
and r.requested_by = u.user_id
order by run_time
/
/* Segments in XAMD and XAMX tablespaces
#
# Purpose : This script list the owner , segment name , segment type , size in MB for
# all the segments in the tablespace ‘XAMD’and ‘XAMX’ .
# Input :
# Output : segment_type, owner, segment_name, size in mb
# */
col segment_name for a35
col segment_type for a15
break on segment_type skip 1 on owner skip 1
compute sum of Used on owner
select segment_type, owner, segment_name, bytes/1024/1024 Used from dba_segments
where tablespace_name in (‘XAMD’, ‘XAMX’)
order by segment_type, owner, Used
/
/*
# the index statistics .
# Purpose : This script gives the index statistics .
# Input :
# Output : table_owner , table_name
# */
set trimspool on feedback off
col owner for a10
col name for a30
col del_lf_rows for 99999999
col lf_rows for 99999999
col per for 999
col height for 99
set pages 999
set lines 100
spool /tmp/x1
select table_owner owner, table_name name
from bapat.perm_index_stat p
where (round( DEL_LF_ROWS * 100/ decode(LF_ROWS,0,1,lf_rows),0) > 50
and del_lf_rows > 99999)
or height > 3
/
spool off
/* List Indexes having 50% Deleted Leaf Rows and deleted rows > 100000
# Purpose : This script gives the index statistics .
# Input :
# Output : owner, name, DEL_LF_ROWS, LF_ROWS ,per,height
# */
set trimspool on feedback off
col owner for a10
col name for a30
col del_lf_rows for 99999999
col lf_rows for 99999999
col per for 999
col height for 99
set pages 999
set lines 100
spool /tmp/x
select owner, name, DEL_LF_ROWS, LF_ROWS ,
round( DEL_LF_ROWS * 100/ decode(LF_ROWS,0,1,lf_rows),0) per, height
from bapat.perm_index_stat p
where (round( DEL_LF_ROWS * 100/ decode(LF_ROWS,0,1,lf_rows),0) > 50
and del_lf_rows > 99999)
or height > 3
order by per
/
spool off
/* Generate Shell Script to Kill Process Accessing a Specific Object
# Purpose : This script creates the scripts /tmp/killusers.sh which has to be run
# from unix which contains the kill command for oracle processes for a
# given database object .
# Input : object name
# Output : kill -9 spid
# */
set heading off
set feedb off
spool /tmp/killusers.sh
select ‘kill -9 ‘||spid
from v$session s, v$process p, v$access a
where a.sid = s.sid
and s.paddr = p.addr
and a.object = upper(‘&Obj_name’)
/
spool off
select ‘Run /tmp/killusers.sh from shell command’ from dual
/
set heading on feedb on
/* Segments Having More than 100 Extents
# Purpose : This script gives the details on all the database segments whose
# max_extents is less than 100 .
# Input :
# Output : owner ,segment name,segment type,extents
# */
col owner for a15
col segment_name for a40
select owner, segment_name, segment_type, extents from dba_segments
where max_extents < 100
/
/* Get Segment Details
# Purpose : This script list the owner, segment_name , partition_name, segment_type ,
# tablespace_name, header_file, header_block, bytes, blocks,
# extents, initial_extent, next_extent, min_extents, max_extents,
# pct_increase, freelists, freelist_groups, relative_fno, buffer_pool
# for a given database segment .
# Input : segment_name
# Output : owner, segment_name , partition_name, segment_type ,
# tablespace_name, header_file, header_block, bytes, blocks,
# extents, initial_extent, next_extent, min_extents, max_extents,
# pct_increase, freelists, freelist_groups, relative_fno, buffer_pool
# */
set pagesize 999
set linesize 132
select * from dba_segments where segment_name = upper(‘&seg_name’)
/
/* List Session Details about a locks on a Specific Table
# Purpose : This script gives the program and oracle session id for a given table .
# Input : object name
# Output : sid
# */
select program,sid
from v$session
where
sid in (select sid from v$lock where id1= (select object_id
from dba_objects
where object_type=’TABLE’ and
object_name = ‘&T_name’)
)
/
/* List Pipe Details
#
# Purpose : This script gives the name,type, pipe size for name not in ‘%-0′ and
# ‘ORA%’ .
# Input :
# Output : name ,type,pipe_size
# */
select substr(name,1,30),type,pipe_size
from
sys.v_$db_pipes
where
name not like ‘%-0′ and
name not like ‘ORA%’
order by pipe_size
/
/* List Sessions With more that 100 DB Block Writes
# Purpose : This script gives pwrites details for the oracle processes .
# Input :
# Output : sid,process,program ,spid,value
# */
col program format a30;
select s.sid,s.process,s.program program,p.spid,st.value PWrites
from v$session s, v$process p, v$sesstat st
where
p.addr=s.paddr
and st.sid = s.sid
and st.statistic#=41
and st.value > 100
order by PWrites Desc
/
/* Get RBS Info
# Purpose : This script list the rollback segment currently being used .The script
# also gives the oracle session details for those processes using the
# rollback segments .
# Input :
# Output :
# */
set lines 200 trimspool on verify off
column program format a25 trunc
column description format a20 trunc
column name format a10 trunc
column username format a15 trunc
column urs_mb heading ‘Used’
column rs_mb heading ‘C.Size’
define blksz=0
column db_blk_sz new_value blksz
set term off
select value blksz
from v$parameter
where name = ‘db_block_size’;
select &&blksz
from dual;
set term on
spool /tmp/roll
select r.name, rssize / ( 1024 * 1024 ) rs_mb,
sum(used_ublk) * &&blksz / ( 1024 * 1024 ) urs_mb
from v$transaction t, v$rollname r, v$rollstat s
where t.xidusn = r.usn
and r.usn = s.usn
group by r.name , rssize;
break on name skip 1 on sid on serial on username
select r.name, username, s.sid, s.serial#,
used_ublk * &&blksz / ( 1024 * 1024 ) used,
object_name, start_time
from v$transaction t, v$rollname r,
v$session s, v$locked_object l,
all_objects o
where t.xidusn = r.usn
and s.taddr=t.addr
and l.session_id = s.sid
and l.xidusn = t.xidusn
and l.xidslot = t.xidslot
and l.xidsqn = t.xidsqn
and l.object_id = o.object_id
order by 1,2,3,4,5;
spool off
/* Get SID and Program from Background Process Id.
# Purpose : This script list the oracle session details for the given
# session id .
# Input : process id
# Output : process id , session id , program ,process
# */
select b.spid, a.sid, a.program, a.process
from v$session a, v$process b
where b.spid = &1
and b.addr = a.paddr
/
/* Get Background Process Info from ForeGround Process Id
# Purpose : This script list the oracle session id , process id ,machine for a
# given a process .
# Input : process
# Output : sid, serial#, spid ,machine
# */
select s.sid, s.serial#, p.spid “B_Process” ,s.machine from v$process p, v$session s
where s.process = ‘&fprocess’
and s.paddr = p.addr
/
/* List Idle Oracle Sessions Originating from Leap App Servers
# Purpose : This script list the oracle session details which has been idling
# long in the servers ‘sclpap07′,’sclpap03′,’sclpap05′,
# ‘sclpap08′,’sclpap06′ .
# Input :
# Output : process,machine,last_call_et
# */
select process,machine,min(last_call_et) from V$SESSION
where type <> ‘BACKGROUND’
and machine in (‘sclpap07′,’sclpap03′,’sclpap05′,’sclpap08′,’sclpap06′)
group by process,machine
having min(last_call_et) > 86400
order by min(last_call_et)
/
/* List Idle Oracle Sessions
# Purpose : This script list the oracle session details which has been idling long .
# Input :
# Output : sid,serial#,process,program,spid
# */
select
sid,a.serial#,process,substr(a.program,1,20),spid
from v$session a,v$process b
where
a.paddr = b.addr
and a.last_call_et> 90000
/
/* List of Objects Locked by a specific SID
# Purpose : This script list the object name,object type , locked mode for a
# given session is with locked mode in (6,3,2) .
# Input : sid
# Output : object_name,object_type,locked_mode
# */
col object_name for a35
select o.object_name,o.object_type,v.locked_mode from dba_objects o,
v$locked_object v
where o.object_id = v.object_id
and v.session_id = &SID
and v.locked_mode in (6,3,2)
/
/* Session Details for a Locked Object
# Purpose : This script gives the details on locks held in the database for a given
# database object .
# Input : object_name
# Output : object_name,object_id,xidusn,,xidslot,xidsqn,session_id,
# oracle_username,os_user_name,process,locked_mode
# */
select object_name,a.object_id,XIDUSN,XIDSLOT,XIDSQN,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from
dba_objects a,v$locked_object b
where a.object_id=b.object_id
and a.object_name like ‘&obj_name%’
/
/* Get BG and FG Process Details for specific SID
# Purpose : This script list the oracle session id , serial number, process id,
# process,program ,machine for a given session id .
# Input : sid
# Output : sid, serial#,spid ,process , program, machine
# */
select s.sid, s.serial#,p.spid “B Process”, s.process “F Process”, s.program, s.machine
from v$process p, v$session s
where sid = &SID
and p.addr = s.paddr
/
/* Select Session Details from SQL Text
# Purpose : This script list the oracle session sid, serial number,program ,
# sql statement for a given sql statment .
# Input : sid
# Output : sid, serial#, program, sql_text
# */
select s.sid, s.serial#, s.program, a.sql_text
from v$session s, v$sqlarea a
where s.sid = &1
and a.address = s.sql_address
/
/* List Session Statistics for a SID
# Purpose : This script list the oracle session id , name ,value for a given
# session id .
# Input : sid
# Output : sid,name,value
# */
select sid, name, value
from v$sesstat ss, v$statname sn
where sn.statistic# = ss.statistic#
and ss.sid = &sid
order by value
/
/* Get Count of Active Sessions, Processes and Wait Events
# Purpose : This script the number of active sessions ,processes ,
# events in the database .
# Input :
# Output : active sessions
# */
@f_l
select count(*) active_session
from v$session
where status = ‘ACTIVE’
/
select count(*) from v$process
/
select count(*), event from v$session_wait group by event
/
/* List Sessions Using Sort Area > 1MB
# Purpose : This script list the oracle session id , serial number , program ,
# process id ,process , blocks , module , sql hash value for
# sort usage greater than 1MB .
# Input :
# Output : sid, serial#, program,spid , process ,blocks, module, sql_hash_value
# */
set trunc on
set linesize 182
col program for a50
select s.sid, s.serial#, substr(s.program,1,15),
p.spid , s.process ,
(sum(blocks ) * 8192) / (1024 * 1024) MB, s.module, s.sql_hash_value
from v$sort_usage su, v$session s, v$process p
where s.serial# = su.session_num
and s.saddr = su.session_addr
and s.paddr = p.addr
group by s.sid, s.serial#, s.program, p.spid, s.process, s.module, s.sql_hash_value
having ( sum(su.blocks) * 8192 ) > ( 1024 * 1024 )
order by 6
/
set trunc off
@/oracle/admin/dba_scripts/f_su
/* Process and Wait Statistics for a SID
# Purpose : This script list the oracle session id , serial number , event ,
# wait text , wait time for a given oracle session id .
# Input : sid
# Output : sid, serial#, process, event, p1text, p1, wait_time,
# seconds_in_wait, state
# */
select s.sid, s.serial#, s.process, sw.event, sw.p1text, sw.p1, sw.wait_time, sw.seconds_in_wait, sw.state
from v$session_wait sw, v$session s
where s.sid = sw.sid
and s.sid = &sid
/
/* List Wait statistics for a Session (SID)
# Purpose : This script list the event , total waits , total timeouts , time waited ,
# average wait , maxiumum wait for a given session id .
# Input : sid
# Output : event, total_waits, total_timeouts, time_waited, average_wait,
# max_wait
# */
set lines 120
col event for a30
select event, total_waits, total_timeouts, time_waited, average_wait, max_wait from v$session_event
where sid = &sid
/
/* Sort Area Usage by Sessions
#
# Purpose : This script list the oracle session id , serial number , program ,
# sort size and time used by the program .
# Input :
# Output : sid,serial#,program,Temp Used,Idle time
# */
col program format a30
col Temp_Used format 99999
select sid,serial#,substr(program,1,25),round(sum(blocks)*8192/(1024*1024),0) Temp_Used,last_call_et/100 Idel_time
from v$session s,v$sort_usage su
where
s.serial# = su.session_num and
s.saddr = su.session_addr
group by sid,serial#,program,last_call_et/100
/
/* Sort Area Usage and Program Details for Each Session
# Purpose : This script list the oracle session id , serial number , program ,
# sort area size used by the program .
# Input :
# Output : sid,serial#,program,Temp Used
# */
col program format a30
col Temp_Used format 99999
select sid,serial#,substr(program,1,25),round(sum(blocks)*8192/(1024*1024),0) Temp_Used,tablespace
from v$session s,v$sort_usage su
where
s.serial# = su.session_num and
s.saddr = su.session_addr
group by sid,serial#,program, tablespace
/
/* Username and Session Details for a BG Process
# Purpose : This script list the oracle process id ,session id , serial number ,
# program , process , operating system user , module for a given
# oracle session id .
# Input : spid
# Output : pid, spid, sid, s.serial#, program, process, osuser, module
# */
select pid, spid, sid, s.serial#, s.program, s.process, s.osuser, s.module
from v$process p, v$session s
where p.spid = &B_process
and p.addr = s.paddr
/
/* Total Sort Area Used
# Purpose : This script gives the current total sort usage of the database .
# Input :
# Output : sum of blocks in MB
# */
select sum(blocks)*8192/1024/1024 MB from v$sort_usage
/
@f_l
select count(*) active_session
from v$session
where status = ‘ACTIVE’
/
select count(*) from v$process
/
select count(*), event from v$session_wait group by event
/
/* Waits For Latch Free
# Purpose : This script list the wait statistics for the free latch event .
# Input :
# Output : total waits ,time waited
# */
set feedb off
select sysdate from dual
/
select sum(total_waits) ,sum(time_waited) from v$system_event
where event = ‘latch free’
/
/* Lists Tablespace Details
# Purpose : This script gives the tablespace details.
# Input :
# Output : Tablespace name,Size in MB
# */
spool /tmp/tsprep.lst
column tablespace_name format a20 head “TS”
column size_in_mb format 999999 head “SZ”
column used_in_mb format 999999 head “Used”
column free_in_mb format 999999 head “Free”
column pctused format 999 head “%”
column max_free format 9999 head “MxFr”
column max_next format 9999 head “MxNx”
column “Database Info ” format a40
col “File Systems” format a60
set pages 999 lines 900 trimspool on
select ‘Database Name : ‘ || name || ‘ ‘ ||
log_mode “Database Info “ from v$database
/
prompt
prompt Datafiles resides on Following File Systems
select distinct substr(name,1, instr(name,’/',-1)-1) “File Systems”
from v$datafile
/
Prompt
select a.tablespace_name,
a.size_in_mb,
(a.size_in_mb – b.free_in_mb) used_in_mb,
b.free_in_mb,
round(((a.size_in_mb – b.free_in_mb)*100/a.size_in_mb), 0) PCTUSED,
b.max_free, c.max_next
from ( select tablespace_name,
round(sum(bytes)/1024/1024, 0) size_in_mb
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name,
round(sum(bytes)/1024/1024, 0) free_in_mb,
max( bytes) / ( 1024 * 1024 ) max_free
from dba_free_space
group by tablespace_name ) b,
( select tablespace_name, max(next_extent) / ( 1024 * 1024 ) max_next
from dba_segments
group by tablespace_name ) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
order by pctused desc
/
Prompt Tablespaces with NO Free Space
select a.tablespace_name from dba_tablespaces a
where a.tablespace_name not in
( select f.tablespace_name from dba_free_space f)
/
spool off
column tablespace_name format a15 head “TS”
column size_in_mb format 999999 head “SZ”
column used_in_mb format 999999 head “Used”
column free_in_mb format 999999 head “Free”
column pctused format 999 head “%”
column max_free format 9999 head “MxFr”
column max_next format 9999 head “MxNx”
column “Database Info ” format a40
col “File Systems” format a60
set heading off
set pages 999 lines 900 trimspool on
select a.tablespace_name,
a.size_in_mb,
(a.size_in_mb – b.free_in_mb) used_in_mb,
b.free_in_mb,
round(((a.size_in_mb – b.free_in_mb)*100/a.size_in_mb), 0) PCTUSED,
b.max_free, c.max_next
from ( select tablespace_name,
round(sum(bytes)/1024/1024, 0) size_in_mb
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name,
round(sum(bytes)/1024/1024, 0) free_in_mb,
max( bytes) / ( 1024 * 1024 ) max_free
from dba_free_space
group by tablespace_name ) b,
( select tablespace_name, max(next_extent) / ( 1024 * 1024 ) max_next
from dba_segments
group by tablespace_name ) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name
and round(((a.size_in_mb – b.free_in_mb)*100/a.size_in_mb), 0) > 80
order by pctused desc
/
exit;
/* Lists Tablespace Details
# Purpose : This script gives the tablespace details even it does not have any segment.
# Input :
# Output : Tablespace name,Size in MB
# */
spool /tmp/tsprep.lst
column tablespace_name format a15 head “TS”
column size_in_mb format 999999 head “SZ”
column used_in_mb format 999999 head “Used”
column free_in_mb format 999999 head “Free”
column pctused format 999 head “%”
column max_free format 9999 head “MxFr”
column max_next format 9999 head “MxNx”
column “Database Info ” format a40
col “File Systems” format a60
set pages 999 lines 900 trimspool on
select ‘Database Name : ‘ || name || ‘ ‘ ||
log_mode “Database Info “ from v$database
/
prompt
prompt Datafiles resides on Following File Systems
select distinct substr(name,1, instr(name,’/',-1)-1) “File Systems”
from v$datafile
/
Prompt
select a.tablespace_name,
a.size_in_mb,
(a.size_in_mb – b.free_in_mb) used_in_mb,
b.free_in_mb,
round(((a.size_in_mb – b.free_in_mb)*100/a.size_in_mb), 0) PCTUSED,
b.max_free, c.max_next
from ( select tablespace_name,
round(sum(bytes)/1024/1024, 0) size_in_mb
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name,
round(sum(bytes)/1024/1024, 0) free_in_mb,
max( bytes) / ( 1024 * 1024 ) max_free
from dba_free_space
group by tablespace_name ) b,
( select tablespace_name, max(next_extent) / ( 1024 * 1024 ) max_next
from dba_segments
group by tablespace_name ) c
where a.tablespace_name = b.tablespace_name
and a.tablespace_name = c.tablespace_name(+)
order by pctused desc
/
Prompt Tablespaces with NO Free Space
select a.tablespace_name from dba_tablespaces a
where a.tablespace_name not in
( select f.tablespace_name from dba_free_space f)
/
spool off
select status, sum(bytes)/1024/1024 IN_MB from dba_undo_extents group by status;
– Find out the current disk space usage for undo transactions –
Select sum(USED_UBLK) * 8192/1024/1024 MB from v$transaction where noundo = ‘NO’ and status = ‘ACTIVE’;
/* Query DBA_WAITERS
#
# Purpose : This script list the waiting session, holding session, lock type,
# mode held,mode requested , lock id1 , lock id2 .
# Input :
# Output : waiting_session, holding_session, lock_type, mode_held,
# mode_requested , lock_id1, lock_id2
# */
column LOCK_TYPE for a12
column MODE_HELD for a10
column MODE_REQUESTED for a10
column LOCK_ID1 for a10
column LOCK_ID2 for a10
select * from dba_waiters
/
No comments:
Post a Comment