VIEW: SYS.DICTIONARY

Source

View Text - Preformatted

select o.name, c.comment$
from sys.obj$ o, sys.com$ c
where o.obj# = c.obj#(+)
  and c.col# is null
  and o.owner# = 0
  and o.type# = 4
  and (o.name like 'USER%'
       or o.name like 'ALL%'
       or (o.name like 'DBA%'
           and exists
                   (select null
                    from sys.v$enabledprivs
                    where priv_number = -47 /* SELECT ANY TABLE */
                    or priv_number = -397 /* READ ANY TABLE */)
           )
      )
union all
select o.name, c.comment$
from sys.obj$ o, sys.com$ c
where o.obj# = c.obj#(+)
  and o.owner# = 0
  and o.name in ('AUDIT_ACTIONS', 'COLUMN_PRIVILEGES', 'DICTIONARY',
        'DICT_COLUMNS', 'DUAL', 'GLOBAL_NAME', 'INDEX_HISTOGRAM',
        'INDEX_STATS', 'RESOURCE_COST', 'ROLE_ROLE_PRIVS', 'ROLE_SYS_PRIVS',
        'ROLE_TAB_PRIVS', 'SESSION_PRIVS', 'SESSION_ROLES',
        'TABLE_PRIVILEGES','NLS_SESSION_PARAMETERS','NLS_INSTANCE_PARAMETERS',
        'NLS_DATABASE_PARAMETERS', 'DATABASE_COMPATIBLE_LEVEL',
        'DBMS_ALERT_INFO', 'DBMS_LOCK_ALLOCATED')
  and c.col# is null
union all
select so.name, 'Synonym for ' || sy.name
from sys.obj$ ro, sys.syn$ sy, sys.obj$ so
where so.type# = 5
  and ro.linkname is null
  and so.owner# = 1
  and so.obj# = sy.obj#
  and so.name <> sy.name
  and sy.owner = 'SYS'
  and sy.name = ro.name
  and ro.owner# = 0
  and ro.type# = 4
  and (ro.owner# = userenv('SCHEMAID')
       or ro.obj# in
           (select oa.obj#
            from sys.objauth$ oa
            where grantee# in (select kzsrorol from x$kzsro))
       or exists (select null from v$enabledprivs
                  where priv_number in (-45 /* LOCK ANY TABLE */,
                                        -47 /* SELECT ANY TABLE */,
                                        -397/* READ ANY TABLE */,
                                        -48 /* INSERT ANY TABLE */,
                                        -49 /* UPDATE ANY TABLE */,
                                        -50 /* DELETE ANY TABLE */)
                  ))

View Text - HTML Formatted

SELECT O.NAME
, C.COMMENT$ FROM SYS.OBJ$ O
, SYS.COM$ C WHERE O.OBJ# = C.OBJ#(+)
AND C.COL# IS NULL
AND O.OWNER# = 0
AND O.TYPE# = 4
AND (O.NAME LIKE 'USER%' OR O.NAME LIKE 'ALL%' OR (O.NAME LIKE 'DBA%'
AND EXISTS (SELECT NULL
FROM SYS.V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -47 /* SELECT ANY TABLE */ OR PRIV_NUMBER = -397 /* READ ANY TABLE */) ) ) UNION ALL SELECT O.NAME
, C.COMMENT$ FROM SYS.OBJ$ O
, SYS.COM$ C WHERE O.OBJ# = C.OBJ#(+)
AND O.OWNER# = 0
AND O.NAME IN ('AUDIT_ACTIONS'
, 'COLUMN_PRIVILEGES'
, 'DICTIONARY'
, 'DICT_COLUMNS'
, 'DUAL'
, 'GLOBAL_NAME'
, 'INDEX_HISTOGRAM'
, 'INDEX_STATS'
, 'RESOURCE_COST'
, 'ROLE_ROLE_PRIVS'
, 'ROLE_SYS_PRIVS'
, 'ROLE_TAB_PRIVS'
, 'SESSION_PRIVS'
, 'SESSION_ROLES'
, 'TABLE_PRIVILEGES'
, 'NLS_SESSION_PARAMETERS'
, 'NLS_INSTANCE_PARAMETERS'
, 'NLS_DATABASE_PARAMETERS'
, 'DATABASE_COMPATIBLE_LEVEL'
, 'DBMS_ALERT_INFO'
, 'DBMS_LOCK_ALLOCATED')
AND C.COL# IS NULL UNION ALL SELECT SO.NAME
, 'SYNONYM FOR ' || SY.NAME FROM SYS.OBJ$ RO
, SYS.SYN$ SY
, SYS.OBJ$ SO WHERE SO.TYPE# = 5
AND RO.LINKNAME IS NULL
AND SO.OWNER# = 1
AND SO.OBJ# = SY.OBJ#
AND SO.NAME <> SY.NAME
AND SY.OWNER = 'SYS'
AND SY.NAME = RO.NAME
AND RO.OWNER# = 0
AND RO.TYPE# = 4
AND (RO.OWNER# = USERENV('SCHEMAID') OR RO.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ))