whatsegment
What EBusiness Suite Database Schema/Segment Summary Space-Used ?
Example:
28-MAY-13 Oracle SegmentSpace Used by SchemaName Page: 1
17:36:09 whatsegment -- Sorted by Tablespace Name
OWNER TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYP BYTES
----- --------------- -------------------------- ----------- -------------
XDO APPS_TS_TX_DATA SYS_IL0004319246C00010$$ LOBINDEX 2,621,440
XDO SYS_LOB0004319246C00010$$ LOBSEGMENT 1,421,213,696
XDO XDO_CONFIG_KEYS TABLE 131,072
XDO XDO_CONFIG_PROPERTIES_B TABLE 131,072
XDO XDO_CONFIG_PROPERTIES_TL TABLE 131,072
XDO XDO_CONFIG_VALUES TABLE 131,072
.
.
.
XDO XDO_FONT_MAPPING_SETS_TL TABLE 131,072
XDO XDO_LOBS TABLE 3,670,016
XDO XDO_TEMPLATES_B TABLE 524,288
XDO XDO_TEMPLATES_TL TABLE 524,288
XDO XDO_TEMPLATE_FIELDS TABLE 1,441,792
XDO XDO_TRANS_UNITS TABLE 9,175,040
XDO XDO_TRANS_UNIT_PROPS TABLE 1,048,576
XDO XDO_TRANS_UNIT_VALUES TABLE 8,519,680
*************** -------------
Total 1,450,835,968
XDO APPS_TS_TX_IDX XDO_DS_DEFINITIONS_B_U1 INDEX 262,144
XDO XDO_DS_DEFINITIONS_TL_U1 INDEX 262,144
XDO XDO_DS_DEFINITIONS_TL_U2 INDEX 393,216
XDO XDO_LOBS_U1 INDEX 786,432
.
.
.
XDO XDO_TRANS_UNITS_U1 INDEX 5,636,096
XDO XDO_TRANS_UNIT_PROPS_U1 INDEX 393,216
XDO XDO_TRANS_UNIT_VALUES_U1 INDEX 6,946,816
*************** -------------
Total 17,432,576
-------------
1,468,268,544
#!/usr/bin/ksh
# whatsegment ()
#-----------------------------------------------------------------------#
# Module Name: whatsegment (c) 2012 #
# #
# Purpose: Report on the Owner-Segments / Space-Used. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ ------------------------------------- #
# 10-May-2012 M.Barone Module design/creation #
#-----------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
clear
echo
echo " #-------------------------------------------------------#"
echo " # whatsegment: Oracle Apps Database Owner-Segments #"
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
read SEGOWN?" Enter the [Segment-Owner] Example: MSC: "
clear
ORANAM=${ORASID}
#-----------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#-----------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT APPS/$PASSWD
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
SPOOL /tmp/whatsegment_$$.txt
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN c_stop NOPRINT
COLUMN c_time NOPRINT
BREAK ON tablespace_name skip 2 ON REPORT
COMPUTE SUM LABEL 'Total' OF bytes ON tablespace_name
COMPUTE SUM LABEL 'Total' OF bytes ON REPORT
COLUMN owner FORMAT A5
COLUMN tablespace_name FORMAT A15
COLUMN segment_name FORMAT A26
COLUMN segment_type FORMAT A15
COLUMN bytes FORMAT 99,999,999,999
TTITLE CENTER 'Oracle SegmentSpace Used by SchemaName' -
LEFT DATEVAR -
RIGHT 'Page:' FORMAT 999 SQL.pno skip 1 -
CENTER 'whatsegment -- Sorted by Tablespace Name' -
LEFT TIMEVAR skip 1
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
owner, tablespace_name,
segment_name, segment_type,
bytes
FROM dba_segments
WHERE owner = UPPER('${SEGOWN}')
ORDER BY owner, tablespace_name, segment_name,
segment_type, bytes;
EXIT;
END_FILE
print
print "Please see /tmp/whatsegment_$$.txt"
print
}
No comments:
Post a Comment