VIEW: SYS.DBA_SQL_PLAN_DIR_OBJECTS

Source

View Text - Preformatted

SELECT
    d.dir_id,
    u.name,
    o.name,
    c.name,
    'COLUMN',
    NULL
FROM
    sys."_BASE_OPT_DIRECTIVE" d,
    sys."_BASE_OPT_FINDING_OBJ_COL" ft,
    (select obj#, owner#, name from sys.obj$
     union all
     select object_id obj#, 0 owner#, name from  v$fixed_table) o,
    (select obj#, intcol#, name from sys.col$
     union all
     select kqfcotob obj#, kqfcocno intcol#, kqfconam name
     from sys.x$kqfco) c,
    sys.user$ u
WHERE
    d.f_id = ft.f_id and ft.f_obj# = o.obj# and o.owner# = u.user#
    and o.obj# = c.obj# and ft.intcol# = c.intcol#
union all
SELECT
    d.dir_id,
    u.name,
    o.name,
    NULL,
    'TABLE',
    fo.notes
FROM
    sys."_BASE_OPT_DIRECTIVE" d,
    sys."_BASE_OPT_FINDING_OBJ" fo,
    (select obj#, owner#, name from sys.obj$
     union all
     select object_id obj#, 0 owner#, name from  v$fixed_table) o,
    sys.user$ u
WHERE
    d.f_id = fo.f_id and fo.f_obj# = o.obj# and o.owner# = u.user#

View Text - HTML Formatted

SELECT D.DIR_ID
, U.NAME
, O.NAME
, C.NAME
, 'COLUMN'
, NULL FROM SYS."_BASE_OPT_DIRECTIVE" D
, SYS."_BASE_OPT_FINDING_OBJ_COL" FT
, (SELECT OBJ#
, OWNER#
, NAME
FROM SYS.OBJ$ UNION ALL SELECT OBJECT_ID OBJ#
, 0 OWNER#
, NAME
FROM V$FIXED_TABLE) O
, (SELECT OBJ#
, INTCOL#
, NAME
FROM SYS.COL$ UNION ALL SELECT KQFCOTOB OBJ#
, KQFCOCNO INTCOL#
, KQFCONAM NAME
FROM SYS.X$KQFCO) C
, SYS.USER$ U WHERE D.F_ID = FT.F_ID
AND FT.F_OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND O.OBJ# = C.OBJ#
AND FT.INTCOL# = C.INTCOL# UNION ALL SELECT D.DIR_ID
, U.NAME
, O.NAME
, NULL
, 'TABLE'
, FO.NOTES FROM SYS."_BASE_OPT_DIRECTIVE" D
, SYS."_BASE_OPT_FINDING_OBJ" FO
, (SELECT OBJ#
, OWNER#
, NAME
FROM SYS.OBJ$ UNION ALL SELECT OBJECT_ID OBJ#
, 0 OWNER#
, NAME
FROM V$FIXED_TABLE) O
, SYS.USER$ U WHERE D.F_ID = FO.F_ID
AND FO.F_OBJ# = O.OBJ#
AND O.OWNER# = U.USER#