#!/usr/bin/ksh
#whatdbfeature ()
#-------------------------------------------------------------------------#
#    Module Name:  whatdbfeature   (c) Copyright May 2012                 #
#                                                                         #
#    Support Notes:                                                       #
#      2276199.1 Avoid the AWR Usage Without the Diagnostic Pack License  #
#      436386.1  Controlling Diagnostic and Tuning Pack Usage             #
#      1909073.1 Disabling and Uninstalling AWR                           #
#                                                                         #
#    Purpose:      Report on the DATABASE_FEATURE_USAGE_STATISTICS.       #
#                  SYSDBA: show parameter  control_management_pack_access #
#                          Values:                                        #
#                          NONE               (No Diagnsotics/NoTuning)   #
#                          DIAGNOSTIC         (Diagnsotics/NoTuning       #
#                          DIAGNOSTIC+TUNING  (Diagnsotics and Tuning     #
#                                                                         #
#                  Oracle Diagnostic Pack:                                #
#                  Oracle Feature Name: Automatic Workload Repository     #
#                                                                         #
#                  Oracle SQL Tuning Packs:                               #
#                  Oracle Feature Name: SQL Access Advisor                #
#                  Oracle Feature Name: SQL Monitoring and Tuning Pages   #
#                  Oracle Feature Name: SQL Performance Analyzer          #
#                  Oracle Feature Name: SQL Plan Management               #
#                  Oracle Feature Name: SQL Profile                       #
#                  Oracle Feature Name: SQL Repair Advisor                #
#                  Oracle Feature Name: SQL Tuning Advisor                #
#                  Oracle Feature Name: SQL Tuning Set (system)           #
#                  Oracle Feature Name: SQL Tuning Set (user)             #
#                  Oracle Feature Name: SQL Workload Manager              #
#                                                                         #
#    Maintenance                                                          #
#    Date          Author        Description                              #
#    -----------   ------------  ---------------------------------------  #
#    10-May-2012   T.Blanford    Module design/creation                   #
#                  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?       #
        #-----------------------------------------------------------------#
        clear
        echo
        echo  " #----------------------------------------------------#"
        echo  " # whatdbfeature:   Oracle Database FeatureStatistics #"
        echo  " #----------------------------------------------------#"
        echo

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

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

        CONNECT / AS SYSDBA

        SPOOL ${SPOOLNM}

        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

        prompt #----------------------------------------------------------#
        prompt #- Oracle Database Features ON/OFF        (whatdbfeature) -#
        prompt #-   CAUTION: Oracle License Needed for:                  -#
        prompt #-            Diagnostics Pack                            -#
        prompt #-            SQL Tuning Advisor                          -#
        prompt #----------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN dname    FORMAT A30      HEADING  "Database Feature Name"
        COLUMN dvern    FORMAT A10      HEADING  "Database|Version"
        COLUMN dused    FORMAT A7       HEADING  "Oracle|Feature|Used"
        COLUMN duage    FORMAT 99999    HEADING  "Oracle|Usage"
        COLUMN dfrst    FORMAT A11      HEADING  "Feature|First|Used"
        COLUMN dlast    FORMAT A11      HEADING  "Feature|Last|Used"

        TTITLE  CENTER  '${ORASID} (${HNAME}) eBusiness Database Features' -
                LEFT    DATEVAR                                 skip 1     -
                CENTER  'eBusiness 12 eBusiness Database Features ON/Off'  -
                LEFT    TIMEVAR                                 skip 2

        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')          timevalue,
		name					dname,
		version					dvern,
		currently_used				dused,
		detected_usages				duage,
		first_usage_date			dfrst,
		last_usage_date				dlast
        FROM    DBA_FEATURE_USAGE_STATISTICS
        ORDER BY name;

END_FILE

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