#!/usr/bin/ksh
# whatsegmenttablespace ()
#------------------------------------------------------------------------------#
#    Module Name:  whatsegmenttablespace                                       #
#                                                                              #
#    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  " # whatsegmenttablespace:  Oracle 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

	read SEGTBSP?"       Enter [7mTablespace-Name[0m Ex: APPS_TS_NOLOGGING: " 

	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/whatsegmenttablespace_$$.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  'whatsegmenttablespace -- 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  	tablespace_name	=  UPPER('${SEGTBSP}')
	ORDER	BY owner, tablespace_name, segment_name, segment_type, bytes;

EXIT;

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