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.ALL_INDEXES

Source

View Text - Preformatted


select u.name, o.name,
       decode(bitand(i.property, 16), 0, '', 'FUNCTION-BASED ') ||
        decode(i.type#, 1, 'NORMAL'||
                          decode(bitand(i.property, 4), 0, '', 4, '/REV'),
                      2, 'BITMAP', 3, 'CLUSTER', 4, 'IOT - TOP',
                      5, 'IOT - NESTED', 6, 'SECONDARY', 7, 'ANSI', 8, 'LOB',
                      9, 'DOMAIN'),
       iu.name, io.name, 'TABLE',
       decode(bitand(i.property, 1), 0, 'NONUNIQUE', 1, 'UNIQUE', 'UNDEFINED'),
       decode(bitand(i.flags, 1073741824), 1073741824, 'ADVANCED HIGH',
              decode(bitand(i.flags, 32), 0, 'DISABLED',
                     decode(bitand(i.flags, 2147483648), 0, 'ENABLED',
                            2147483648, 'ADVANCED LOW'))),
       i.spare2,
       decode(bitand(i.property, 34), 0, decode(i.type#, 9, null, ts.name),
           2, null, decode(i.ts#, 0, null, ts.name)),
       decode(bitand(i.property, 2),0, i.initrans, null),
       decode(bitand(i.property, 2),0, i.maxtrans, null),
       decode(bitand(i.flags, 67108864), 67108864,
                     ds.initial_stg * ts.blocksize,
                     s.iniexts * ts.blocksize),
       decode(bitand(i.flags, 67108864), 67108864,
              ds.next_stg * ts.blocksize,
              s.extsize * ts.blocksize),
       decode(bitand(i.flags, 67108864), 67108864,
              ds.minext_stg, s.minexts),
       decode(bitand(i.flags, 67108864), 67108864,
              ds.maxext_stg, s.maxexts),
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
              decode(bitand(i.flags, 67108864), 67108864,
                            ds.pctinc_stg, s.extpct)),
       decode(i.type#, 4, mod(i.pctthres$,256), NULL), i.trunccnt,
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
       decode(bitand(o.flags, 2), 2, 1,
              decode(bitand(i.flags, 67108864), 67108864,
                     decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
                     decode(s.lists, 0, 1, s.lists)))),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(bitand(o.flags, 2), 2, 1,
                     decode(bitand(i.flags, 67108864), 67108864,
                            decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                            decode(s.groups, 0, 1, s.groups)))),
       decode(bitand(i.property, 2),0,i.pctfree$,null),
       decode(bitand(i.property, 2), 2, NULL,
                decode(bitand(i.flags, 4), 0, 'YES', 'NO')),
       i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac,
       decode(bitand(i.property, 2), 2,
                   decode(i.type#, 9, decode(bitand(i.flags, 8),
                                        8, 'INPROGRS', 'VALID'), 'N/A'),
                     decode(bitand(i.flags, 1), 1, 'UNUSABLE',
                            decode(bitand(i.flags, 8), 8, 'INRPOGRS',
                                                            'VALID'))),
       rowcnt, samplesize, analyzetime,
       decode(i.degree, 32767, 'DEFAULT', nvl(i.degree,1)),
       decode(i.instances, 32767, 'DEFAULT', nvl(i.instances,1)),
       decode(bitand(i.property, 2), 2, 'YES', 'NO'),
       decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
       decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
       decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(i.flags, 67108864), 67108864,
                            ds.bfp_stg, s.cachehint), 3),
                            1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(i.flags, 67108864), 67108864,
                            ds.bfp_stg, s.cachehint), 12)/4,
                            1, 'KEEP', 2, 'NONE', 'DEFAULT')),
       decode(bitand(o.flags, 2), 2, 'DEFAULT',
              decode(bitand(decode(bitand(i.flags, 67108864), 67108864,
                            ds.bfp_stg, s.cachehint), 48)/16,
                            1, 'KEEP', 2, 'NONE', 'DEFAULT')),
       decode(bitand(i.flags, 64), 0, 'NO', 'YES'),
       decode(bitand(o.flags, 2), 0, NULL,
           decode(bitand(i.property, 64), 64, 'SYS$SESSION', 'SYS$TRANSACTION')),
       decode(bitand(i.flags, 128), 128, mod(trunc(i.pctthres$/256),256),
              decode(i.type#, 4, mod(trunc(i.pctthres$/256),256), NULL)),
       itu.name, ito.name, i.spare4,
       decode(bitand(i.flags, 2048), 0, 'NO', 'YES'),
       decode(i.type#, 9, decode(o.status, 5, 'IDXTYP_INVLD',
                                           1, 'VALID'),  ''),
       decode(i.type#, 9, decode(bitand(i.flags, 16), 16, 'FAILED', 'VALID'), ''),
       decode(bitand(i.property, 16), 0, '',
              decode(bitand(i.flags, 1024), 0, 'ENABLED', 'DISABLED')),
       decode(bitand(i.property, 1024), 1024, 'YES', 'NO'),
       decode(bitand(i.property, 16384), 16384, 'YES', 'NO'),
       decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
       decode(bitand(i.flags,2097152),2097152,'INVISIBLE','VISIBLE'),
       decode(i.type#, 9, decode(bitand(i.property, 2048), 2048,
                               'SYSTEM_MANAGED', 'USER_MANAGED'), ''),
       decode(bitand(i.flags, 67108864), 67108864, 'NO',
              decode(bitand(i.property, 2), 2, 'N/A', 'YES')),
       decode(bitand(i.flags, 268435456), 268435456, 'YES', 'NO'),
       decode(bitand(i.flags, 8388608), 8388608, 'PARTIAL', 'FULL')
from sys.ts$ ts, sys.seg$ s, sys.user$ iu, sys.obj$ io,
     sys.user$ u, sys.ind$ i, sys.obj$ o, sys.user$ itu, sys.obj$ ito,
     sys.deferred_stg$ ds
where u.user# = o.owner#
  and o.obj# = i.obj#
  and i.bo# = io.obj#
  and io.owner# = iu.user#
  and io.type# = 2 /* tables */
  and bitand(i.flags, 4096) = 0
  and bitand(o.flags, 128) = 0
  and i.ts# = ts.ts# (+)
  and i.file# = s.file# (+)
  and i.block# = s.block# (+)
  and i.ts# = s.ts# (+)
  and i.obj# = ds.obj# (+)
  and i.type# in (1, 2, 3, 4, 6, 7, 9)
  and i.indmethod# = ito.obj# (+)
  and ito.owner# = itu.user# (+)
  and (io.owner# = userenv('SCHEMAID')
        or
       io.obj# in ( select obj#
                    from sys.objauth$
                    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 U.NAME
, O.NAME
, DECODE(BITAND(I.PROPERTY
, 16)
, 0
, ''
, 'FUNCTION-BASED ') || DECODE(I.TYPE#
, 1
, 'NORMAL'|| DECODE(BITAND(I.PROPERTY
, 4)
, 0
, ''
, 4
, '/REV')
, 2
, 'BITMAP'
, 3
, 'CLUSTER'
, 4
, 'IOT - TOP'
, 5
, 'IOT - NESTED'
, 6
, 'SECONDARY'
, 7
, 'ANSI'
, 8
, 'LOB'
, 9
, 'DOMAIN')
, IU.NAME
, IO.NAME
, 'TABLE'
, DECODE(BITAND(I.PROPERTY
, 1)
, 0
, 'NONUNIQUE'
, 1
, 'UNIQUE'
, 'UNDEFINED')
, DECODE(BITAND(I.FLAGS
, 1073741824)
, 1073741824
, 'ADVANCED HIGH'
, DECODE(BITAND(I.FLAGS
, 32)
, 0
, 'DISABLED'
, DECODE(BITAND(I.FLAGS
, 2147483648)
, 0
, 'ENABLED'
, 2147483648
, 'ADVANCED LOW')))
, I.SPARE2
, DECODE(BITAND(I.PROPERTY
, 34)
, 0
, DECODE(I.TYPE#
, 9
, NULL
, TS.NAME)
, 2
, NULL
, DECODE(I.TS#
, 0
, NULL
, TS.NAME))
, DECODE(BITAND(I.PROPERTY
, 2)
, 0
, I.INITRANS
, NULL)
, DECODE(BITAND(I.PROPERTY
, 2)
, 0
, I.MAXTRANS
, NULL)
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.INITIAL_STG * TS.BLOCKSIZE
, S.INIEXTS * TS.BLOCKSIZE)
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.NEXT_STG * TS.BLOCKSIZE
, S.EXTSIZE * TS.BLOCKSIZE)
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.MINEXT_STG
, S.MINEXTS)
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.MAXEXT_STG
, S.MAXEXTS)
, DECODE(BITAND(TS.FLAGS
, 3)
, 1
, TO_NUMBER(NULL)
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.PCTINC_STG
, S.EXTPCT))
, DECODE(I.TYPE#
, 4
, MOD(I.PCTTHRES$
, 256)
, NULL)
, I.TRUNCCNT
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 1
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DECODE(DS.FRLINS_STG
, 0
, 1
, DS.FRLINS_STG)
, DECODE(S.LISTS
, 0
, 1
, S.LISTS))))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 1
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
, DECODE(S.GROUPS
, 0
, 1
, S.GROUPS))))
, DECODE(BITAND(I.PROPERTY
, 2)
, 0
, I.PCTFREE$
, NULL)
, DECODE(BITAND(I.PROPERTY
, 2)
, 2
, NULL
, DECODE(BITAND(I.FLAGS
, 4)
, 0
, 'YES'
, 'NO'))
, I.BLEVEL
, I.LEAFCNT
, I.DISTKEY
, I.LBLKKEY
, I.DBLKKEY
, I.CLUFAC
, DECODE(BITAND(I.PROPERTY
, 2)
, 2
, DECODE(I.TYPE#
, 9
, DECODE(BITAND(I.FLAGS
, 8)
, 8
, 'INPROGRS'
, 'VALID')
, 'N/A')
, DECODE(BITAND(I.FLAGS
, 1)
, 1
, 'UNUSABLE'
, DECODE(BITAND(I.FLAGS
, 8)
, 8
, 'INRPOGRS'
, 'VALID')))
, ROWCNT
, SAMPLESIZE
, ANALYZETIME
, DECODE(I.DEGREE
, 32767
, 'DEFAULT'
, NVL(I.DEGREE
, 1))
, DECODE(I.INSTANCES
, 32767
, 'DEFAULT'
, NVL(I.INSTANCES
, 1))
, DECODE(BITAND(I.PROPERTY
, 2)
, 2
, 'YES'
, 'NO')
, DECODE(BITAND(O.FLAGS
, 2)
, 0
, 'N'
, 2
, 'Y'
, 'N')
, DECODE(BITAND(O.FLAGS
, 4)
, 0
, 'N'
, 4
, 'Y'
, 'N')
, DECODE(BITAND(O.FLAGS
, 16)
, 0
, 'N'
, 16
, 'Y'
, 'N')
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 'DEFAULT'
, DECODE(BITAND(DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.BFP_STG
, S.CACHEHINT)
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT'))
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 'DEFAULT'
, DECODE(BITAND(DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT'))
, DECODE(BITAND(O.FLAGS
, 2)
, 2
, 'DEFAULT'
, DECODE(BITAND(DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT'))
, DECODE(BITAND(I.FLAGS
, 64)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(O.FLAGS
, 2)
, 0
, NULL
, DECODE(BITAND(I.PROPERTY
, 64)
, 64
, 'SYS$SESSION'
, 'SYS$TRANSACTION'))
, DECODE(BITAND(I.FLAGS
, 128)
, 128
, MOD(TRUNC(I.PCTTHRES$/256)
, 256)
, DECODE(I.TYPE#
, 4
, MOD(TRUNC(I.PCTTHRES$/256)
, 256)
, NULL))
, ITU.NAME
, ITO.NAME
, I.SPARE4
, DECODE(BITAND(I.FLAGS
, 2048)
, 0
, 'NO'
, 'YES')
, DECODE(I.TYPE#
, 9
, DECODE(O.STATUS
, 5
, 'IDXTYP_INVLD'
, 1
, 'VALID')
, '')
, DECODE(I.TYPE#
, 9
, DECODE(BITAND(I.FLAGS
, 16)
, 16
, 'FAILED'
, 'VALID')
, '')
, DECODE(BITAND(I.PROPERTY
, 16)
, 0
, ''
, DECODE(BITAND(I.FLAGS
, 1024)
, 0
, 'ENABLED'
, 'DISABLED'))
, DECODE(BITAND(I.PROPERTY
, 1024)
, 1024
, 'YES'
, 'NO')
, DECODE(BITAND(I.PROPERTY
, 16384)
, 16384
, 'YES'
, 'NO')
, DECODE(BITAND(O.FLAGS
, 128)
, 128
, 'YES'
, 'NO')
, DECODE(BITAND(I.FLAGS
, 2097152)
, 2097152
, 'INVISIBLE'
, 'VISIBLE')
, DECODE(I.TYPE#
, 9
, DECODE(BITAND(I.PROPERTY
, 2048)
, 2048
, 'SYSTEM_MANAGED'
, 'USER_MANAGED')
, '')
, DECODE(BITAND(I.FLAGS
, 67108864)
, 67108864
, 'NO'
, DECODE(BITAND(I.PROPERTY
, 2)
, 2
, 'N/A'
, 'YES'))
, DECODE(BITAND(I.FLAGS
, 268435456)
, 268435456
, 'YES'
, 'NO')
, DECODE(BITAND(I.FLAGS
, 8388608)
, 8388608
, 'PARTIAL'
, 'FULL') FROM SYS.TS$ TS
, SYS.SEG$ S
, SYS.USER$ IU
, SYS.OBJ$ IO
, SYS.USER$ U
, SYS.IND$ I
, SYS.OBJ$ O
, SYS.USER$ ITU
, SYS.OBJ$ ITO
, SYS.DEFERRED_STG$ DS WHERE U.USER# = O.OWNER#
AND O.OBJ# = I.OBJ#
AND I.BO# = IO.OBJ#
AND IO.OWNER# = IU.USER#
AND IO.TYPE# = 2 /* TABLES */
AND BITAND(I.FLAGS
, 4096) = 0
AND BITAND(O.FLAGS
, 128) = 0
AND I.TS# = TS.TS# (+)
AND I.FILE# = S.FILE# (+)
AND I.BLOCK# = S.BLOCK# (+)
AND I.TS# = S.TS# (+)
AND I.OBJ# = DS.OBJ# (+)
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 9)
AND I.INDMETHOD# = ITO.OBJ# (+)
AND ITO.OWNER# = ITU.USER# (+)
AND (IO.OWNER# = USERENV('SCHEMAID') OR IO.OBJ# IN ( SELECT OBJ#
FROM SYS.OBJAUTH$
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 */) ) )