whatschemastats
What EBusiness Suite Database Schema Statistics History (Execution Dates).
What EBusiness Suite Database Schema Statistics History (Execution Elapsed Time).
Example:
#---------------------------------------------------------------#
#- What (whatschemastats) -#
#---------------------------------------------------------------#
28-MAY-13 OATC (oratest) Apps Gather Schema Statistics
17:45:36 EBusiness Gather Schema Statistics History
Schema Schema Elapsed Time
Scheam Name Start Time End-Time Duration
--------------- ------------------ ------------------ ------------------
ABM 09-MAY-13 03:01:57 09-MAY-13 03:02:04 00:00:07
AHL 24-MAY-13 01:14:19 24-MAY-13 01:14:36 00:00:17
AHM 09-MAY-13 03:02:06 09-MAY-13 03:02:06 00:00:00
AK 24-MAY-13 01:14:36 24-MAY-13 01:15:01 00:00:25
ALR 24-MAY-13 01:15:01 24-MAY-13 01:15:14 00:00:13
AMF 09-MAY-13 03:02:25 09-MAY-13 03:02:25 00:00:00
AMS 24-MAY-13 01:15:14 24-MAY-13 01:15:52 00:00:38
AMV 24-MAY-13 01:15:52 24-MAY-13 01:16:01 00:00:09
AMW 24-MAY-13 01:16:01 24-MAY-13 01:16:16 00:00:15
AP 24-MAY-13 01:16:16 24-MAY-13 01:36:03 00:19:47
APPLSYS 24-MAY-13 01:36:03 24-MAY-13 01:44:24 00:08:21
APPS 24-MAY-13 01:44:24 24-MAY-13 01:47:23 00:02:59
AR 24-MAY-13 01:47:23 24-MAY-13 01:54:03 00:06:40
.
.
.
XLA 24-MAY-13 06:14:53 24-MAY-13 07:46:13 01:31:20
XLE 24-MAY-13 07:46:13 24-MAY-13 07:46:15 00:00:02
XNB 24-MAY-13 07:46:15 24-MAY-13 07:46:16 00:00:01
XNC 09-MAY-13 03:56:57 09-MAY-13 03:56:57 00:00:00
XNI 09-MAY-13 03:56:57 09-MAY-13 03:56:58 00:00:01
XNM 09-MAY-13 03:56:58 09-MAY-13 03:56:58 00:00:00
XNP 24-MAY-13 07:46:16 24-MAY-13 07:46:23 00:00:07
XNS 09-MAY-13 03:56:59 09-MAY-13 03:56:59 00:00:00
XTR 24-MAY-13 07:46:23 24-MAY-13 07:46:48 00:00:25
XXEIS 31-JAN-13 03:06:41 31-JAN-13 03:08:22 00:01:41
ZFA 24-MAY-13 07:46:48 24-MAY-13 07:46:48 00:00:00
ZPB 24-MAY-13 07:46:48 24-MAY-13 07:46:57 00:00:09
ZSA 24-MAY-13 07:46:57 24-MAY-13 07:46:57 00:00:00
ZX 24-MAY-13 07:46:57 24-MAY-13 07:53:59 00:07:02
#!/usr/bin/ksh
#whatschemastats ()
#-----------------------------------------------------------------------#
# Module Name: whatschemastats (c) 2012 #
# #
# Purpose: Report on the FND_NODES #
# ADMIN-Server ConcMgr-Server #
# Forms-Server Web-Server #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ ------------------------------------- #
# 10-May-2012 S.Bomareddy Module design/creation #
#-----------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
#---------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#---------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------#"
echo " # whatschemastats: Gather Schema Stats History #"
echo " #--------------------------------------------------#"
echo
#-----------------------------------------------------------#
# Trap: Set CNTL-C CNTL-D CNTL-\Before Password Protection #
#-----------------------------------------------------------#
trap "stty echo; return" 2
trap "stty echo; return" 3
stty -echo
read PASSWD?" Please Enter the Oracle APPS Passwd: "
stty echo
echo
#-----------------------------------------------------------#
# Trap: Reset CNTL-C CNTL-D CNTL-\ After Passwd Protection #
#-----------------------------------------------------------#
trap 2
trap 3
read SCHDAT?" Enter the FROM-DATE (DD-MON-YY): "
clear
else
PASSWD=$1
clear
fi
#-----------------------------------------------------------------------#
# Initialize #
#-----------------------------------------------------------------------#
SPOOLNM=/tmp/whatschemastats_$$.txt
#-----------------------------------------------------------------------#
# Variables #
#-----------------------------------------------------------------------#
if [[ SCHDAT == '' ]]
then
SCHDAT='01-JAN-10'
fi
#-----------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#-----------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT APPS/$PASSWD
SPOOL ${SPOOLNM}
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 80
set pagesize 999
prompt #--------------------------------------------------------#
prompt #- What (whatschemastats) -#
prompt #--------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN sowner format A15 HEADING 'Scheam Name'
COLUMN strtim format A18 HEADING 'Schema|Start Time'
COLUMN endtim format A18 HEADING 'Schema|End-Time'
COLUMN stimes format A18 HEADING 'Elapsed Time|Duration'
TTITLE CENTER '${ORASID} (${HNAME}) Gather Schema Statistics ' -
LEFT DATEVAR skip 1 -
CENTER 'EBusiness Schema Statistics History' -
LEFT TIMEVAR skip 2
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
SCHEMA_NAME sowner,
TO_CHAR(LAST_GATHER_START_TIME,'DD-MON-YY HH24:MI:SS') strtim,
to_char(LAST_GATHER_END_TIME,'DD-MON-YY HH24:MI:SS') endtim,
to_char(trunc(sysdate) + (nvl(LAST_GATHER_END_TIME,sysdate)
- LAST_GATHER_START_TIME),'hh24:mi:ss') stimes
FROM applsys.FND_STATS_HIST
WHERE LAST_GATHER_DATE >= '${SCHDAT}'
AND upper(SCHEMA_NAME) like '%'
AND object_type = 'SCHEMA'
ORDER BY sowner, strtim;
END_FILE
print
print "Please see /tmp/whatschemastats_$$.txt"
print
}
No comments:
Post a Comment