VIEW: SYS.DBA_SQL_PLAN_DIRECTIVES

Source

View Text - Preformatted

SELECT
    d.dir_id,
    d.type,
    d.enabled,
    case when d.internal_state = 'HAS_STATS' or d.redundant = 'YES'
           then 'SUPERSEDED'
         when d.internal_state in ('NEW', 'MISSING_STATS', 'PERMANENT')
           then 'USABLE'
         else 'UNKNOWN' end case,
    d.auto_drop,
    f.reason,
    d.created,
    d.last_modified,
    d.last_used,
    xmltype(
      '' ||
         '' || d.internal_state || '' ||
         '' || d.redundant || '' ||
         '' || sys.dbms_spd_internal.get_spd_text(d.dir_id) ||
         '' ||
       '')  notes
FROM
    sys."_BASE_OPT_DIRECTIVE" d,
    sys."_BASE_OPT_FINDING" f
WHERE d.f_id = f.f_id

View Text - HTML Formatted

SELECT D.DIR_ID
, D.TYPE
, D.ENABLED
, CASE WHEN D.INTERNAL_STATE = 'HAS_STATS' OR D.REDUNDANT = 'YES' THEN 'SUPERSEDED' WHEN D.INTERNAL_STATE IN ('NEW'
, 'MISSING_STATS'
, 'PERMANENT') THEN 'USABLE' ELSE 'UNKNOWN' END CASE
, D.AUTO_DROP
, F.REASON
, D.CREATED
, D.LAST_MODIFIED
, D.LAST_USED
, XMLTYPE( '' || '' || D.INTERNAL_STATE || '' || '' || D.REDUNDANT || '' || '' || SYS.DBMS_SPD_INTERNAL.GET_SPD_TEXT(D.DIR_ID) || '' || '') NOTES FROM SYS."_BASE_OPT_DIRECTIVE" D
, SYS."_BASE_OPT_FINDING" F WHERE D.F_ID = F.F_ID