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 <sid> <serial#> ;

# */

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

/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s