#!/usr/bin/ksh
# whatsegmenttotal ()
#------------------------------------------------------------------------------#
#    Module Name:  whatsegmenttotal                                            #
#                                                                              #
#    Purpose:      Report on the Owner-Segments / Space-Used.                  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
	clear
	echo
	echo  " #--------------------------------------------------------#"
	echo  " # whatsegmenttotal:  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 [7mAPPS[0m Passwd: " 
            
	stty echo
	echo
	#------------------------------------------------------------------#
	#  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
	#------------------------------------------------------------------#
	trap        2
	trap        3

	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/whatsegmenttotal_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  c_stop       NOPRINT 
        COLUMN  c_time       NOPRINT

	BREAK   ON  byts  skip     0          ON REPORT
	COMPUTE SUM LABEL 'Total'  OF  byts   ON REPORT

	COLUMN  fasn     FORMAT  A10     -
	Heading "Appl|Short|Name"

	COLUMN  fapn     FORMAT  A50    -
	Heading "Application Name"

	COLUMN  byts     FORMAT  999,999,999,999      -
	Heading "Total|Bytes"

        TTITLE  CENTER  'Oracle SegmentSpace Used by SchemaName'	-
                LEFT    DATEVAR						-
		RIGHT   'Page:'         FORMAT 999 SQL.pno	skip 1	-
                CENTER  'whatsegmenttotal -- Sorted by Schema Name' 	-
                LEFT    TIMEVAR                                 skip 1  

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
		DBAS.OWNER		fasn,
		FNDA.APPLICATION_NAME	fapn,
		sum(DBAS.bytes) 	byts
	FROM	DBA_SEGMENTS		DBAS,
		FND_APPLICATION_ALL_VIEW	FNDA
	WHERE	DBAS.owner		=  FNDA.APPLICATION_SHORT_NAME(+)
	GROUP BY  DBAS.OWNER, FNDA.APPLICATION_NAME
	ORDER BY  DBAS.owner;

EXIT;

END_FILE
print
print "Please see /tmp/whatsegmenttotal_$$.txt"
print
}
