#!/usr/bin/ksh
#whatfndlob_extents ()
#------------------------------------------------------------------------------#
#    Module Name:  whatfndlob_extents                                          #
#                                                                              #
#    Purpose:      Report on the E-Business Suite FND_LOBS (Extents).          #
#                                                                              #
#    Notes:        Normally DELETED-ROWS in a TABLE-Space can be Cleared:      #
#                  ALTER TABLE tablename ENABLE ROW MOVEMENT;                  #
#                  ALTER TABLE tablename SHRINK SPACE;                         #
#                  -or-                                                        #
#                  ALTER TABLE tablename SHRINK SPACE COMPACT;                 #
#                                                                              #
#                  However, when the TABLE contains a LOB-COLUMN with unused   #
#                  space (Oracle 10g R2 and following), then use this:         #
#                                                                              #
#                  ALTER TABLE fnd_lobs MODIFY LOB (filedata) (SHRINK SPACE);  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    22-Aug-2016   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
       	ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
       	HNAME=$(uname -n)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
       	if      [ $# = 0 ]
       	then
       	    clear
       	    echo
       	    echo  " #--------------------------------------------------------#"
       	    echo  " # whatfndlob_extents:      Oracle FND_LOBS Large Extents #"
       	    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 SEGNAM?"        Enter SegmentName [SYS_LOB0000239111C00014$]:"

       	    clear
       	else
       	    PASSWD=$1
       	    clear
       	fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
       	SPOOLNM=/tmp/whatfndlob_extents_$$.txt

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

       	CONNECT  APPS/$PASSWD

        set     pagesize        9000
       	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 ${SPOOLNM}

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

       	prompt #---------------------------------------------------------------#
       	prompt #- E-Business (whatfndlob_extents)                             -#
       	prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN segnam  FORMAT A30        HEADING 'Segment Name'
        COLUMN segtyp  FORMAT A17        HEADING 'Segment Type'
        COLUMN extids  FORMAT 99999999   HEADING 'Extent-ID'
        COLUMN extbyt  FORMAT 999999999  HEADING 'Extent|Bytes|Size(Mb)'

        TTITLE  CENTER  '${ORASID} (${HNAME}) FND_LOBS Extents'		      -
                LEFT    DATEVAR                                     skip 1    -
               	CENTER  'Sorted by ExtentSize(SUM)'                           -
                LEFT    TIMEVAR                                     skip 2

       	SET    	TIMING ON

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')  		datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS') 		timevalue,
       		SEGMENT_NAME   				segnam,
       		SEGMENT_TYPE   				segtyp,
       		EXTENT_ID      				extids,
       		BYTES,  SUM(BYTES) OVER
       		( PARTITION BY SEGMENT_NAME, SEGMENT_TYPE )/1024/1024  	extbyt
       		FROM   	DBA_EXTENTS
       		WHERE  	TABLESPACE_NAME = 'APPS_TS_MEDIA'
       		  AND  	SEGMENT_NAME    = '${SEGNAM}'
       		ORDER BY SEGMENT_NAME, SEGMENT_TYPE, EXTENT_ID;


EXIT;
END_FILE

print
print  "Please see  /tmp/whatfndlob_extents_$$.txt"
print
}
