eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.KU$_ALL_INDEX_VIEW

Source

View Text - Preformatted


select '1','5',
         i.obj#, value(o),
         cast(multiset(select * from ku$_index_col_view ic
                       where ic.obj_num = i.obj#
                        order by ic.pos_num
                      ) as ku$_index_col_list_t
             ),
         ts.name, ts.blocksize,
         (select value(s) from ku$_storage_view s
          where i.file#  = s.file_num
          and   i.block# = s.block_num
          and   i.ts#    = s.ts_num),
         (select value(s) from ku$_deferred_stg_view s
          where s.obj_num = i.obj#),
         i.dataobj#, i.bo#,
         (select value(so) from ku$_schemaobj_view so
          where so.obj_num = i.bo#),
         (select value(ao) from ku$_schemaobj_view ao
          where ao.obj_num = dbms_metadata_util.get_anc(i.bo#,0)),
         i.indmethod#,
         (select o2.name from obj$ o2 where i.indmethod# = o2.obj#),
         (select u2.name from user$ u2
                where u2.user# = (select o3.owner# from obj$ o3
                                        where i.indmethod# = o3.obj#)),
         -- include domain index info if type# = 9 (cooperative index method)
         decode(i.type#, 9,
           cast(multiset(select * from ku$_domidx_2ndtab_view so
                        where so.obj_num=i.obj#
                        ) as ku$_domidx_2ndtab_list_t
                ),
           null),
         decode(i.type#, 9,
           (select value(pl) from ku$_domidx_plsql_view pl
                where pl.obj_num = i.obj#),
           null),
         -- include bitmap join index info if this is a bji
         decode(bitand(i.property, 1024), 1024,
           cast(multiset(select * from ku$_jijoin_table_view j
                        where j.obj_num = i.obj#
                        ) as ku$_jijoin_table_list_t
                ),
           null),
         decode(bitand(i.property, 1024), 1024,
           cast(multiset(select * from ku$_jijoin_view j
                        where j.obj_num = i.obj#
                        ) as ku$_jijoin_list_t
                ),
           null),
         i.cols, i.pctfree$,
         i.initrans, i.maxtrans, i.pctthres$, i.type#, i.flags, i.property,
         i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac,
         to_char(i.analyzetime,'YYYY/MM/DD HH24:MI:SS'), i.samplesize, i.rowcnt, i.intcols, i.degree,
         i.instances, i.trunccnt, i.spare1, i.spare2,
         (select value(po) from ku$_ind_partobj_view po
          where i.obj# = po.obj_num),
         i.spare3, replace(i.spare4, chr(0)), i.spare5, to_char(i.spare6,'YYYY/MM/DD HH24:MI:SS'),
         nvl((select 1 from cdef$ c
              where c.enabled = i.obj# and
                    c.type# = 2 and
                    (select 1 from tab$ t
                     where t.obj# = c.obj# and
                     bitand(t.property, 4096) = 4096) = 1) ,0),
         -- and index is used for reference partitioning if it is used
         --  for a primary or unique key constraint and 0x400 is set in
         --  defer (ref par parent).
         nvl((select 1 from dual where
          (exists (select 1 from cdef$ c
              where c.enabled = i.obj# and
                    c.type# in(2, 3) and
                    (bitand(c.defer,1024)!=0)))),0),
         nvl((select ic.oid_or_setid from ku$_index_col_view ic
              where i.type#=1
              and i.intcols=1
              and ic.obj_num=i.obj#),0),
          nvl((select bitand(t.property, 4294967295)
               from tab$ t where t.obj# = i.bo#),0),
          nvl((select trunc(t.property / power(2, 32))
               from tab$ t where t.obj# = i.bo#),0)
   from  ku$_schemaobj_view o, ind$ i, ts$ ts
   where o.obj_num = i.obj#
         AND  i.ts# = ts.ts#
         AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner_num, 0) OR
              EXISTS ( SELECT * FROM sys.session_roles
                       WHERE role='SELECT_CATALOG_ROLE' ))

View Text - HTML Formatted

SELECT '1'
, '5'
, I.OBJ#
, VALUE(O)
, CAST(MULTISET(SELECT *
FROM KU$_INDEX_COL_VIEW IC
WHERE IC.OBJ_NUM = I.OBJ# ORDER BY IC.POS_NUM ) AS KU$_INDEX_COL_LIST_T )
, TS.NAME
, TS.BLOCKSIZE
, (SELECT VALUE(S)
FROM KU$_STORAGE_VIEW S
WHERE I.FILE# = S.FILE_NUM
AND I.BLOCK# = S.BLOCK_NUM
AND I.TS# = S.TS_NUM)
, (SELECT VALUE(S)
FROM KU$_DEFERRED_STG_VIEW S
WHERE S.OBJ_NUM = I.OBJ#)
, I.DATAOBJ#
, I.BO#
, (SELECT VALUE(SO)
FROM KU$_SCHEMAOBJ_VIEW SO
WHERE SO.OBJ_NUM = I.BO#)
, (SELECT VALUE(AO)
FROM KU$_SCHEMAOBJ_VIEW AO
WHERE AO.OBJ_NUM = DBMS_METADATA_UTIL.GET_ANC(I.BO#
, 0))
, I.INDMETHOD#
, (SELECT O2.NAME
FROM OBJ$ O2
WHERE I.INDMETHOD# = O2.OBJ#)
, (SELECT U2.NAME
FROM USER$ U2
WHERE U2.USER# = (SELECT O3.OWNER#
FROM OBJ$ O3
WHERE I.INDMETHOD# = O3.OBJ#))
, -- INCLUDE DOMAIN INDEX INFO IF TYPE# = 9 (COOPERATIVE INDEX METHOD) DECODE(I.TYPE#
, 9
, CAST(MULTISET(SELECT *
FROM KU$_DOMIDX_2NDTAB_VIEW SO
WHERE SO.OBJ_NUM=I.OBJ# ) AS KU$_DOMIDX_2NDTAB_LIST_T )
, NULL)
, DECODE(I.TYPE#
, 9
, (SELECT VALUE(PL)
FROM KU$_DOMIDX_PLSQL_VIEW PL
WHERE PL.OBJ_NUM = I.OBJ#)
, NULL)
, -- INCLUDE BITMAP JOIN INDEX INFO IF THIS IS A BJI DECODE(BITAND(I.PROPERTY
, 1024)
, 1024
, CAST(MULTISET(SELECT *
FROM KU$_JIJOIN_TABLE_VIEW J
WHERE J.OBJ_NUM = I.OBJ# ) AS KU$_JIJOIN_TABLE_LIST_T )
, NULL)
, DECODE(BITAND(I.PROPERTY
, 1024)
, 1024
, CAST(MULTISET(SELECT *
FROM KU$_JIJOIN_VIEW J
WHERE J.OBJ_NUM = I.OBJ# ) AS KU$_JIJOIN_LIST_T )
, NULL)
, I.COLS
, I.PCTFREE$
, I.INITRANS
, I.MAXTRANS
, I.PCTTHRES$
, I.TYPE#
, I.FLAGS
, I.PROPERTY
, I.BLEVEL
, I.LEAFCNT
, I.DISTKEY
, I.LBLKKEY
, I.DBLKKEY
, I.CLUFAC
, TO_CHAR(I.ANALYZETIME
, 'YYYY/MM/DD HH24:MI:SS')
, I.SAMPLESIZE
, I.ROWCNT
, I.INTCOLS
, I.DEGREE
, I.INSTANCES
, I.TRUNCCNT
, I.SPARE1
, I.SPARE2
, (SELECT VALUE(PO)
FROM KU$_IND_PARTOBJ_VIEW PO
WHERE I.OBJ# = PO.OBJ_NUM)
, I.SPARE3
, REPLACE(I.SPARE4
, CHR(0))
, I.SPARE5
, TO_CHAR(I.SPARE6
, 'YYYY/MM/DD HH24:MI:SS')
, NVL((SELECT 1
FROM CDEF$ C
WHERE C.ENABLED = I.OBJ# AND C.TYPE# = 2 AND (SELECT 1
FROM TAB$ T
WHERE T.OBJ# = C.OBJ# AND BITAND(T.PROPERTY
, 4096) = 4096) = 1)
, 0)
, --
AND INDEX IS USED FOR REFERENCE PARTITIONING IF IT IS USED -- FOR A PRIMARY OR UNIQUE KEY CONSTRAINT
AND 0X400 IS SET IN -- DEFER (REF PAR PARENT). NVL((SELECT 1
FROM DUAL WHERE (EXISTS (SELECT 1
FROM CDEF$ C
WHERE C.ENABLED = I.OBJ# AND C.TYPE# IN(2
, 3) AND (BITAND(C.DEFER
, 1024)!=0))))
, 0)
, NVL((SELECT IC.OID_OR_SETID
FROM KU$_INDEX_COL_VIEW IC
WHERE I.TYPE#=1
AND I.INTCOLS=1
AND IC.OBJ_NUM=I.OBJ#)
, 0)
, NVL((SELECT BITAND(T.PROPERTY
, 4294967295)
FROM TAB$ T
WHERE T.OBJ# = I.BO#)
, 0)
, NVL((SELECT TRUNC(T.PROPERTY / POWER(2
, 32))
FROM TAB$ T
WHERE T.OBJ# = I.BO#)
, 0)
FROM KU$_SCHEMAOBJ_VIEW O
, IND$ I
, TS$ TS
WHERE O.OBJ_NUM = I.OBJ#
AND I.TS# = TS.TS#
AND (SYS_CONTEXT('USERENV'
, 'CURRENT_USERID') IN (O.OWNER_NUM
, 0) OR EXISTS ( SELECT *
FROM SYS.SESSION_ROLES
WHERE ROLE='SELECT_CATALOG_ROLE' ))