VIEW: SYS.USER_PART_INDEXES

Source

View Text - Preformatted

select io.name, o.name,
        decode(po.parttype, 1, 'RANGE', 2, 'HASH', 3, 'SYSTEM', 4, 'LIST',
                            5, 'REFERENCE', 'UNKNOWN'),
        decode(mod(po.spare2, 256), 0, 'NONE', 1, 'RANGE', 2, 'HASH',
                                    3, 'SYSTEM', 4, 'LIST', 5, 'REFERENCE',
                                    'UNKNOWN'),
       po.partcnt, mod(trunc(po.spare2/65536), 65536),
       po.partkeycols,  mod(trunc(po.spare2/256), 256),
       decode(bitand(po.flags, 1), 1, 'LOCAL',    'GLOBAL'),
       decode(po.partkeycols, 0, 'NONE', decode(bitand(po.flags,2), 2, 'PREFIXED', 'NON_PREFIXED')),
       ts.name, po.defpctfree, po.definitrans,
       po.defmaxtrans,
       decode(po.deftiniexts, NULL, 'DEFAULT', po.deftiniexts),
       decode(po.defextsize, NULL, 'DEFAULT', po.defextsize),
       decode(po.defminexts, NULL, 'DEFAULT', po.defminexts),
       decode(po.defmaxexts, NULL, 'DEFAULT', po.defmaxexts),
       decode(po.defmaxsize, NULL, 'DEFAULT', po.defmaxsize),
       decode(po.defextpct, NULL, 'DEFAULT', po.defextpct),
       po.deflists, po.defgroups,
       decode(po.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       decode(bitand(po.spare1, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(po.spare1, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(po.spare1, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       po.parameters,
       case when (po.parttype = 5 and bitand(po.flags, 64) = 64) then 'YES'
            else po.interval_str end
from   sys.obj$ io, sys.obj$ o, sys.partobj$ po, sys.ts$ ts, sys.ind$ i
where  io.obj# = po.obj# and po.defts# = ts.ts# (+) and i.obj# = io.obj#
       and o.obj# = i.bo# and io.owner# = userenv('SCHEMAID') and
       io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL
       and io.subname IS NULL

View Text - HTML Formatted

SELECT IO.NAME
, O.NAME
, DECODE(PO.PARTTYPE
, 1
, 'RANGE'
, 2
, 'HASH'
, 3
, 'SYSTEM'
, 4
, 'LIST'
, 5
, 'REFERENCE'
, 'UNKNOWN')
, DECODE(MOD(PO.SPARE2
, 256)
, 0
, 'NONE'
, 1
, 'RANGE'
, 2
, 'HASH'
, 3
, 'SYSTEM'
, 4
, 'LIST'
, 5
, 'REFERENCE'
, 'UNKNOWN')
, PO.PARTCNT
, MOD(TRUNC(PO.SPARE2/65536)
, 65536)
, PO.PARTKEYCOLS
, MOD(TRUNC(PO.SPARE2/256)
, 256)
, DECODE(BITAND(PO.FLAGS
, 1)
, 1
, 'LOCAL'
, 'GLOBAL')
, DECODE(PO.PARTKEYCOLS
, 0
, 'NONE'
, DECODE(BITAND(PO.FLAGS
, 2)
, 2
, 'PREFIXED'
, 'NON_PREFIXED'))
, TS.NAME
, PO.DEFPCTFREE
, PO.DEFINITRANS
, PO.DEFMAXTRANS
, DECODE(PO.DEFTINIEXTS
, NULL
, 'DEFAULT'
, PO.DEFTINIEXTS)
, DECODE(PO.DEFEXTSIZE
, NULL
, 'DEFAULT'
, PO.DEFEXTSIZE)
, DECODE(PO.DEFMINEXTS
, NULL
, 'DEFAULT'
, PO.DEFMINEXTS)
, DECODE(PO.DEFMAXEXTS
, NULL
, 'DEFAULT'
, PO.DEFMAXEXTS)
, DECODE(PO.DEFMAXSIZE
, NULL
, 'DEFAULT'
, PO.DEFMAXSIZE)
, DECODE(PO.DEFEXTPCT
, NULL
, 'DEFAULT'
, PO.DEFEXTPCT)
, PO.DEFLISTS
, PO.DEFGROUPS
, DECODE(PO.DEFLOGGING
, 0
, 'NONE'
, 1
, 'YES'
, 2
, 'NO'
, 'UNKNOWN')
, DECODE(BITAND(PO.SPARE1
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(PO.SPARE1
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(PO.SPARE1
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, PO.PARAMETERS
, CASE WHEN (PO.PARTTYPE = 5
AND BITAND(PO.FLAGS
, 64) = 64) THEN 'YES' ELSE PO.INTERVAL_STR END FROM SYS.OBJ$ IO
, SYS.OBJ$ O
, SYS.PARTOBJ$ PO
, SYS.TS$ TS
, SYS.IND$ I WHERE IO.OBJ# = PO.OBJ#
AND PO.DEFTS# = TS.TS# (+)
AND I.OBJ# = IO.OBJ#
AND O.OBJ# = I.BO#
AND IO.OWNER# = USERENV('SCHEMAID') AND IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL
AND IO.SUBNAME IS NULL