VIEW: SYS.ALL_DIMENSIONS

Source

View Text - Preformatted

select u.name, o.name,
       decode(o.status, 5, 'Y', 'N'),
       decode(o.status, 1, 'VALID', 5, 'NEEDS_COMPILE', 'ERROR'),
       1                  /* Metadata revision number */
from sys.dim$ d, sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.obj# = d.obj#
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DECODE(O.STATUS
, 5
, 'Y'
, 'N')
, DECODE(O.STATUS
, 1
, 'VALID'
, 5
, 'NEEDS_COMPILE'
, 'ERROR')
, 1 /* METADATA REVISION NUMBER */ FROM SYS.DIM$ D
, SYS.OBJ$ O
, SYS.USER$ U WHERE O.OWNER# = U.USER#
AND O.OBJ# = D.OBJ#
AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-215 /* CREATE ANY DIMENSION */
, -216 /* ALTER ANY DIMENSION */
, -217 /* DROP ANY DIMENSION */) ) )