#!/usr/bin/ksh
# whattableextents ()
#------------------------------------------------------------------------------#
#    Module Name:  whattableextents                                            #
#                                                                              #
#    Purpose:      Report on the Tablespace Extent 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  " # whattableextents:    Oracle 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 SEGNM?"        Enter Segment-Name Ex: FND_FLEX_VALUES "

       	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/whattableextents_$$.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 	tsownr 	FORMAT A8     	HEADING 'Extnt|Owner'
       	COLUMN 	tssegn 	FORMAT A8     	HEADING 'Extnt|Segment|Name'
       	COLUMN 	tspart 	FORMAT A5     	HEADING 'Extnt|Part|ition|Name'
       	COLUMN 	tstype 	FORMAT A5     	HEADING 'Extent|Type'
       	COLUMN 	tsname 	FORMAT A10     	HEADING 'Tablespace|Name'
       	COLUMN 	tsextn 	FORMAT 999   	HEADING 'Ext|ID'
       	COLUMN 	tsfile 	FORMAT 9999  	HEADING 'Extnt|FleID'
       	COLUMN 	tsblid 	FORMAT 999999  	HEADING 'Extnt|BlkID'
       	COLUMN 	tsbyte 	FORMAT 99999999	HEADING 'Extnt|Bytes'
       	COLUMN 	tsblks 	FORMAT 999	HEADING 'Extnt|Blcks'
       	COLUMN 	tsrelf 	FORMAT 999	HEADING 'Rel|Fle|No'


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

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
       		owner				tsownr,
       		segment_name			tssegn,
       		partition_name 			tspart,
       		segment_type   			tstype,
       		tablespace_name			tsname,
       		extent_id	      		tsextn,
       		file_id				tsfile,
       		block_id		       	tsblid,
       		bytes     			tsbyte,
       		blocks     			tsblks,
       		relative_fno			tsrelf
       	 FROM  	dba_extents
       	WHERE  	segment_name	 LIKE  UPPER('%${SEGNM}%')
       	ORDER  	BY tsownr, tssegn, tsextn;

EXIT;

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