whatapps
What EBusiness Suite Products Use Which Tablespaces (Default and Temp).
What EBusiness Suite Products correlate to Application-Schema-ID.
What EBusiness Suite Products correlate to Oracle-Schema-ID.
Example:
#---------------------------------------------------------------#
#- What Apps Products/Default and TempTablespace (whatapps) -#
#---------------------------------------------------------------#
11-JUN-13 OATC (oratest) Apps Details Tablespace
14:39:50 E-Business Apps Product Tablespace
Oracle App App FND App FND DBA DBA
User APP Short Base Oracle Grp Read DBA Default Temp
ID ID Name Path UserNm No. Only Use Tablespace Tblsp
------ ------ ----- -------- ------- ---- ---- --- --------------- ------
867 867 AHL AHL_TOP AHL 1 A Yes APPS_TS_TX_DATA TEMP
601 601 AK AK_TOP AK 0 A Yes APPS_TS_TX_DATA TEMP
160 160 ALR ALR_TOP ALR 0 A Yes APPS_TS_TX_DATA TEMP
530 530 AMS AMS_TOP AMS 1 A Yes APPS_TS_TX_DATA TEMP
520 520 AMV AMV_TOP AMV 1 A Yes APPS_TS_TX_DATA TEMP
242 242 AMW AMW_TOP AMW 0 A Yes APPS_TS_TX_DATA TEMP
222 222 AR AR_TOP AR 1 A Yes APPS_TS_TX_DATA TEMP
.
.
.
20066 EDWREP X No Unknown Unknown
20065 ODM X No Unknown Unknown
20069 PORTAL30 X No Unknown Unknown
20067 CTXSYS X Yes CTXSYS TEMP
#!/usr/bin/ksh
#whatapps ()
#-----------------------------------------------------------------------#
# Module Name: whatapps Copyright (c) 2012 -- All Rights Reserved #
# #
# Purpose: Report on Apps (Products/Default/Temp Tablespaces) #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ ------------------------------------- #
# 25-May-2010 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 " # whatapps: E-Business Tablespaces Default/Temp #"
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
clear
else
PASSWD=$1
clear
fi
#-----------------------------------------------------------------------#
# Initialize #
#-----------------------------------------------------------------------#
SPOOLNM=/tmp/whatapps_$$.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 Apps Products/Default/Temp Tablespace (whatapps)-#
prompt #--------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN fouid FORMAT 99999 HEADING "Oracle|User|ID"
COLUMN appid FORMAT 99999 HEADING "APP|ID"
COLUMN appsn FORMAT A5 HEADING "App|Short|Name"
COLUMN appbp FORMAT A8 HEADING "App|Base|Path"
COLUMN fouun FORMAT A7 HEADING "FND|Oracle|UserNm"
COLUMN fouig FORMAT 999 HEADING "App|Grp|No."
COLUMN fouro FORMAT A4 HEADING "FND|Read|Only"
COLUMN dbuun FORMAT A3 HEADING "DBA|User"
COLUMN dbuts FORMAT A15 HEADING "DBA|Default|Tablespace"
COLUMN dbutt FORMAT A10 HEADING "DBA|Temp|Tablespace"
TTITLE CENTER '${ORASID} (${HNAME}) Apps Detail Tablespace ' -
LEFT DATEVAR skip 1 -
CENTER 'E-Business 12 Apps Details Products' -
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,
fndu.oracle_id fouid,
fnda.application_id appid,
decode(nvl(fnda.application_short_name, 'Not Found'),
'SQLAP','AP','SQLGL','GL','OFA','FA',
fnda.application_short_name) appsn,
fnda.basepath appbp,
fndu.oracle_username fouun,
fndu.install_group_num fouig,
fndu.read_only_flag fouro,
decode(nvl(dbau.username,' X '),' X ','No','Yes') dbuun,
nvl(dbau.default_tablespace,'Unknown') dbuts,
nvl(dbau.temporary_tablespace,'Unknown') dbutt
FROM fnd_application fnda,
dba_users dbau,
fnd_oracle_userid fndu
WHERE dbau.username(+) = fndu.oracle_username
AND fnda.application_short_name(+) = fndu.oracle_username
ORDER BY 5;
END_FILE
print
print "Please see /tmp/whatapps_$$.txt"
print
}
No comments:
Post a Comment