#!/usr/bin/ksh
# whattablespace ()
#------------------------------------------------------------------------------#
#    Module Name:  whattablespace                                              #
#                                                                              #
#    Purpose:      Report on the Tablespace Definition Details.                #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

       	clear
       	echo
       	echo  " #--------------------------------------------------------#"
       	echo  " # whattablespace:  Oracle Apps Database Tablespace Detail #"
       	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 TBSPCE?"        Enter Tablespace-Name Ex: APPS_UNDOTS1: "

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

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

       	BREAK   ON  REPORT
       	COMPUTE SUM LABEL 'Total'  OF  bytes   	ON segment_name
       	COMPUTE SUM LABEL 'Total'  OF  bytes   	ON REPORT

       	COLUMN 	tsname 	FORMAT A15     	HEADING 'Tablespace|Name'
       	COLUMN 	extmgt 	FORMAT A10     	HEADING 'Extent|Management'
       	COLUMN 	alltyp 	FORMAT A15     	HEADING 'Allocation|Type'
       	COLUMN 	segmgt 	FORMAT A15     	HEADING 'Segment|Space|Management'
       	COLUMN 	blksiz 	FORMAT 99999999	HEADING 'Block|Size'
       	COLUMN 	iniext 	FORMAT 99999999	HEADING 'Initial|Extent'
       	COLUMN 	nxtext 	FORMAT 99999999	HEADING 'Next|Extent'
       	COLUMN 	minext 	FORMAT 99999999	HEADING 'Minimum|Extents'
       	COLUMN 	maxext 	FORMAT 99999999999 HEADING 'Maximum|Extents'
       	COLUMN 	maxsiz 	FORMAT 99999999	HEADING 'Maximum|Size'
       	COLUMN 	pctinc 	FORMAT 99999   	HEADING 'Percent|Increase'


        TTITLE  CENTER  'Oracle Tablespace Details'    			-
                LEFT    DATEVAR						-
       		RIGHT   'Page:'         FORMAT 999 SQL.pno     	skip 1 	-
                CENTER  'whattablespace -- Sorted by Tablespace'       	-
                LEFT    TIMEVAR                                 skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
       		tablespace_name			tsname,
       		extent_management      		extmgt,
       		allocation_type			alltyp,
       		segment_space_management       	segmgt,
       		block_size     			blksiz
       	 FROM  	dba_tablespaces
       	WHERE  	tablespace_name	 LIKE  UPPER('${TBSPCE}')
       	ORDER  	BY tablespace_name;


        TTITLE  CENTER  '                       ' skip 1
        SELECT  tablespace_name			tsname,
       		initial_extent 			iniext,
       		next_extent    			nxtext,
       		min_extents    			minext,
       		max_extents    			maxext,
       		max_size       			maxsiz,
       		pct_increase   			pctinc
       	 FROM  	dba_tablespaces
       	WHERE  	tablespace_name	 LIKE  UPPER('${TBSPCE}')
       	ORDER  	BY tablespace_name;

EXIT;

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