#!/usr/bin/ksh
# whatdbfeatureusage ()
#------------------------------------------------------------------------------#
#    Module Name:  whatdbfeatureusage   (c) May 2012                           #
#                                                                              #
#    Purpose:      Report on the DBA_FEATURE_USAGE_STATISTICS                  #
#                  Oracle Audit and Oracle Licensed Management Services (LMS)  #
#                  will compare the DBA__FEATURE_USAGE_STATISTICS View and     #
#                  the Oracle-Customer's License Agreement in order to         #
#                  evauluate some of the License/Feature violations.           #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    12-Apr-2019   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

        clear
        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------------#"
            echo  " # whatdbfeatureusage:    Oracle Feature Usage Statistics #"
            echo  " #--------------------------------------------------------#"
            echo

            print
            echo  " Connecting as SYSDBA"
            sleep 2

            clear
        else
            SEARCH=$1
            clear
        fi

        ORANAM=${ORASID}

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

	CONNECT  /  as  SYSDBA

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


	PROMPT	#--------------------------------------------------------#
	PROMPT	# Oracle Diagnostic Package and Oracle Tuning Package:   #
	PROMPT	#   DIAGNOSTICS License is needed for AWR-Reports.       #
	PROMPT	#   TUNING      License is needed for ASH-Reports.       #
	PROMPT	#--------------------------------------------------------#
        TTITLE  CENTER  '${ORANAM} (${HNAME}) Diagnostics and Tuning Packages' -
									skip 2

        COLUMN  NAME_COL_PLUS_SHOW_PARAM	FORMAT A30  Heading 'Parameter'
        COLUMN  TYPE				FORMAT A6   Heading 'Type'
        COLUMN  VALUE_COL_PLUS_SHOW_PARAM	FORMAT A38  Heading 'Value'

	SHOW PARAMETER PACK

	PROMPT
	PROMPT
	PROMPT	#--------------------------------------------------------#
	PROMPT	# Oracle DBA Feature Usage Statistics:                   #
	PROMPT	#   Oracle Audit and Oracle Licensed Management Services #
	PROMPT	#   compares the DBA__FEATURE_USAGE_STATISTICS View and  #
	PROMPT	#   the Oracle-Customer's License Agreement in order to  #
	PROMPT	#   evaulate some of the License/Feature violations.     #
	PROMPT	#--------------------------------------------------------#
        TTITLE  CENTER  '${ORANAM} (${HNAME}) Oracle DB Chargable Features'   -
									skip 2

        COLUMN  DBID			FORMAT 9999999999  Heading 'DBID'
        COLUMN  NAME			FORMAT A25    Heading 'FeatureName'
        COLUMN  VERSION		FORMAT A10    Heading 'Version'
        COLUMN  CURRENTLY_USED		FORMAT A9     Heading 'Currently|Used'
        COLUMN  DETECTED_USAGES	FORMAT 999    Heading 'Usage'
        COLUMN  AUX_COUNT		FORMAT 99999  Heading 'Count'
        COLUMN  TOTAL_SAMPLES		FORMAT 999999 Heading 'Total|Samples'
        COLUMN  FIRST_USAGE_DATE	FORMAT A9     Heading 'First|Used'
        COLUMN  LAST_USAGE_DATE	FORMAT A9     Heading 'Last|Used'
        COLUMN  FEATURE_INFO		FORMAT A24    Heading 'Info'
        COLUMN  LAST_SAMPLE_DATE	FORMAT 999999 Heading 'Last|Sampled'
        COLUMN  DESCRIPTION		FORMAT A25    Heading 'Descrption'

        SELECT	DBID,
		NAME,
		VERSION,
		CURRENTLY_USED,
		DETECTED_USAGES,
		AUX_COUNT,
		TOTAL_SAMPLES,
		FIRST_USAGE_DATE,
		LAST_USAGE_DATE,
		FEATURE_INFO,
		LAST_SAMPLE_DATE,
		DESCRIPTION
        FROM	DBA_FEATURE_USAGE_STATISTICS
        ORDER BY  DBID, NAME, VERSION;

EXIT;

END_FILE


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