#!/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
}
