VIEW: SYS.ALL_XML_INDEXES

Source

View Text - Preformatted

select
   u.name         INDEX_OWNER,
   oi.name        INDEX_NAME,
   bu.name        TABLE_OWNER,
   bo.name        TABLE_NAME,
   case when bitand(p.flags, 536870912)=536870912 then 'REPOSITORY'
        when bitand(p.flags, 4096)=4096 then 'BINARY'
        when bitand(p.flags, 8192)=8192 then 'CLOB in OR'
        when bitand(p.flags, 16384)=16384 then 'CLOB'
        else 'OR' end TYPE,
   case when bitand(p.flags , 268435456 ) != 268435456 then 'STRUCTURED'
        when bitand(p.flags, 268435456 ) = 268435456 and exists (select xt.idxobj# from xdb.xdb$xtab xt where xt.idxobj# = p.idxobj#) then 'STRUCTURED and UNSTRUCTURED'
        else 'UNSTRUCTURED' end INDEX_TYPE,
   case when bitand(p.flags,  268435456 ) != 268435456 then ''
        else     ot.name  end PATH_TABLE_NAME,
   p.parameters   PARAMETERS,
   case when bitand(p.flags, 65011712)=6291456 then 'ON-COMMIT'
        when bitand(p.flags, 65011712)=10485760 then 'MANUAL'
        when bitand(p.flags, 65011712)=18874368 then 'EVERY'
        else 'ALWAYS' end ASYNC,
   case when bitand(p.flags, 2097152)=2097152 then 'TRUE'
        else 'FALSE' end STALE,
   case when bitand(p.flags, 2097152)=2097152 then
        (select op.name from sys.obj$ op
         where  op.obj# = p.pendtabobj#)
        else '' end PEND_TABLE_NAME,
   case when bitand(p.flags, 32)=32 then 'INCLUDE'
        when bitand(p.flags, 128)=128 then 'EXCLUDE'
        else 'FULLY IX' end EX_or_INCLUDE
 from xdb.xdb$dxptab p, sys.obj$ ot, sys.obj$ oi, sys.user$ u,
      sys.user$ bu, sys.obj$ bo, sys.ind$ i
 where oi.owner# = u.user# and
       oi.obj# = p.idxobj# and p.pathtabobj# = ot.obj# and
       i.obj# = oi.obj# and i.bo# = bo.obj# and bo.owner# = bu.user# and
       (u.user# = userenv('SCHEMAID')
        or oi.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 (-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 U.NAME INDEX_OWNER
, OI.NAME INDEX_NAME
, BU.NAME TABLE_OWNER
, BO.NAME TABLE_NAME
, CASE WHEN BITAND(P.FLAGS
, 536870912)=536870912 THEN 'REPOSITORY' WHEN BITAND(P.FLAGS
, 4096)=4096 THEN 'BINARY' WHEN BITAND(P.FLAGS
, 8192)=8192 THEN 'CLOB IN OR' WHEN BITAND(P.FLAGS
, 16384)=16384 THEN 'CLOB' ELSE 'OR' END TYPE
, CASE WHEN BITAND(P.FLAGS
, 268435456 ) != 268435456 THEN 'STRUCTURED' WHEN BITAND(P.FLAGS
, 268435456 ) = 268435456
AND EXISTS (SELECT XT.IDXOBJ#
FROM XDB.XDB$XTAB XT
WHERE XT.IDXOBJ# = P.IDXOBJ#) THEN 'STRUCTURED
AND UNSTRUCTURED' ELSE 'UNSTRUCTURED' END INDEX_TYPE
, CASE WHEN BITAND(P.FLAGS
, 268435456 ) != 268435456 THEN '' ELSE OT.NAME END PATH_TABLE_NAME
, P.PARAMETERS PARAMETERS
, CASE WHEN BITAND(P.FLAGS
, 65011712)=6291456 THEN 'ON-COMMIT' WHEN BITAND(P.FLAGS
, 65011712)=10485760 THEN 'MANUAL' WHEN BITAND(P.FLAGS
, 65011712)=18874368 THEN 'EVERY' ELSE 'ALWAYS' END ASYNC
, CASE WHEN BITAND(P.FLAGS
, 2097152)=2097152 THEN 'TRUE' ELSE 'FALSE' END STALE
, CASE WHEN BITAND(P.FLAGS
, 2097152)=2097152 THEN (SELECT OP.NAME
FROM SYS.OBJ$ OP
WHERE OP.OBJ# = P.PENDTABOBJ#) ELSE '' END PEND_TABLE_NAME
, CASE WHEN BITAND(P.FLAGS
, 32)=32 THEN 'INCLUDE' WHEN BITAND(P.FLAGS
, 128)=128 THEN 'EXCLUDE' ELSE 'FULLY IX' END EX_OR_INCLUDE
FROM XDB.XDB$DXPTAB P
, SYS.OBJ$ OT
, SYS.OBJ$ OI
, SYS.USER$ U
, SYS.USER$ BU
, SYS.OBJ$ BO
, SYS.IND$ I
WHERE OI.OWNER# = U.USER# AND OI.OBJ# = P.IDXOBJ#
AND P.PATHTABOBJ# = OT.OBJ# AND I.OBJ# = OI.OBJ#
AND I.BO# = BO.OBJ#
AND BO.OWNER# = BU.USER# AND (U.USER# = USERENV('SCHEMAID') OR OI.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 (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */)))