whatinvaliddetail
What EBusiness Suite Database Objects are INVALID -- Details
Example:
#---------------------------------------------------------------#
#- What (whatinvaliddetail) -#
#---------------------------------------------------------------#
OATC (oratest) Apps Invalid Objects Page: 1
whatinvaliddetail -- Sorted by Schema Name
Owner
Schema Object Name Object Type
---------- ---------------------------------------- --------------------
APPS AHL_UTIL_UC_PKG PACKAGE BODY
AP_CCE_BANK_UPGRADE PACKAGE BODY
AP_PAYMENT_EVENT_WF_PKG PACKAGE BODY
AR_CREDIT_CHECK PACKAGE
AR_CREDIT_CHECK PACKAGE BODY
AR_CREDIT_USAGE_RULE_SETS_VL VIEW
AR_INVOICE_API_PUB PACKAGE BODY
ASO_CC_ENCRYPT_CONC_REQ_PVT PACKAGE BODY
AW_OAE3 PACKAGE
AW_OAE3 PACKAGE BODY
AW_USER_PROC PROCEDURE
CSE_PROJ_TRANSFER_PKG PACKAGE BODY
CSL_MTL_SYSTEM_ITEMS_ACC_PKG PACKAGE BODY
CS_ROUTING_UTL PACKAGE BODY
SYS Q$LATCH VIEW
Q$LATCHSUM VIEW
#!/usr/bin/ksh
#whatinvaliddetail ()
#-----------------------------------------------------------------------#
# Module Name: whatinvaliddetail (c) May 2012 #
# #
# Purpose: Report on the Invalid Objects by Schama. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ ------------------------------------- #
# 10-Oct-2009 M.Barone 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 " # whatinvaliddetail: Oracle Apps Forms Users #"
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 Password Protection #
#------------------------------------------------------------#
trap 2
trap 3
clear
else
PASSWD=$1
clear
fi
#-----------------------------------------------------------------------#
# Initialize #
#-----------------------------------------------------------------------#
SPOOLNM=/tmp/whatinvaliddetail_$$.txt
#-----------------------------------------------------------------------#
# 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 (whatinvaliddetail) -#
prompt #--------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
BREAK ON REPORT
BREAK ON owner skip 2 ON REPORT
COMPUTE SUM LABEL 'Total' OF objcnt ON OWNER
COMPUTE SUM LABEL 'Total' OF objcnt ON REPORT
COLUMN OWNER format A10 Heading "Owner|Schema"
COLUMN OBJECT_NAME format A40 Heading "Object Name"
COLUMN OBJECT_TYPE format A20 Heading "Object Type"
COLUMN OBJCNT format 999999999 Heading "Count"
TTITLE CENTER '${ORASID} (${HNAME}) Apps Invalid Objects' -
RIGHT 'Page:' FORMAT 999 SQL.pno skip 1 -
CENTER 'whatinvaliddetail -- Sorted by Schema' skip 2
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT DISTINCT owner, object_name, object_type
FROM dba_objects
WHERE STATUS <> 'VALID'
ORDER BY 1,2;
END_FILE
print
print "Please see /tmp/whatinvaliddetail_$$.txt"
print
}
No comments:
Post a Comment