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

Source

View Text - Preformatted


select u.name, po.name, po.subname, so.subname,
       isp.hiboundval, isp.hiboundlen,
       row_number() over (partition by u.name, po.name, po.subname
                          order by isp.subpart#),
       decode(bitand(isp.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name,
       isp.pctfree$, isp.initrans, isp.maxtrans,
       decode(bitand(isp.flags, 65536), 65536,
              ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize),
       decode(bitand(isp.flags, 65536), 65536,
              ds.next_stg * ts.blocksize, s.extsize * ts.blocksize),
       decode(bitand(isp.flags, 65536), 65536, ds.minext_stg, s.minexts),
       decode(bitand(isp.flags, 65536), 65536, ds.maxext_stg, s.maxexts),
       decode(bitand(isp.flags, 65536), 65536,
              ds.maxsiz_stg * ts.blocksize,
              decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)),
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
              decode(bitand(isp.flags, 65536), 65536,
                     ds.pctinc_stg, s.extpct)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(bitand(isp.flags, 65536), 65536,
                     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(isp.flags, 65536), 65536,
                     decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                     decode(s.groups, 0, 1, s.groups))),
       decode(mod(trunc(isp.flags / 4), 2), 0, 'YES', 'NO'),
       case
         when bitand(isp.flags, 1024) = 1024 then 'ENABLED'
       else
         case when (bitand(isp.flags, 65536) = 65536) then
           decode(bitand(ds.flags_stg, 4), 4,
                  decode(bitand(ds.cmpflag_stg, 6),
                         4, 'ADVANCED LOW',
                         2, 'ADVANCED HIGH',
                         NULL),
                  'DISABLED')
         else
           decode(bitand(s.spare1, 2048), 2048,
                  decode(bitand(s.spare1, 16777216 + 1048576),
                         16777216, 'ADVANCED HIGH',
                         1048576, 'ADVANCED LOW',
                         NULL),
                  'DISABLED')
         end
       end,
       isp.blevel, isp.leafcnt, isp.distkey, isp.lblkkey, isp.dblkkey,
       isp.clufac, isp.rowcnt, isp.samplesize, isp.analyzetime,
       decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3),
              1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(decode(bitand(isp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(isp.flags, 8), 0, 'NO', 'YES'),
       decode(bitand(isp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(isp.flags, 32768), 32768, 'YES', 'NO'),
       decode(bitand(isp.flags, 65536), 65536, 'NO', 'YES')
from   sys.obj$ so, sys.obj$ po, sys.indcompart$ icp, sys.indsubpart$ isp,
       sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.ind$ i, sys.tab$ t,
       sys.deferred_stg$ ds
where  so.obj# = isp.obj# and po.obj# = icp.obj# and
       icp.obj# = isp.pobj# and isp.ts# = ts.ts# and u.user# = po.owner# and
       isp.file# = s.file#(+) and isp.block# = s.block#(+) and
       isp.ts# = s.ts#(+) and isp.obj# = ds.obj#(+) and
       icp.bo# = i.obj# and i.bo# = t.obj# and
       bitand(icp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       bitand(isp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       bitand(t.trigflag, 1073741824) != 1073741824
       and po.namespace = 4 and po.remoteowner IS NULL and po.linkname IS NULL
       and so.namespace = 4 and so.remoteowner IS NULL and so.linkname IS NULL

View Text - HTML Formatted

SELECT U.NAME
, PO.NAME
, PO.SUBNAME
, SO.SUBNAME
, ISP.HIBOUNDVAL
, ISP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, PO.NAME
, PO.SUBNAME ORDER BY ISP.SUBPART#)
, DECODE(BITAND(ISP.FLAGS
, 1)
, 1
, 'UNUSABLE'
, 'USABLE')
, TS.NAME
, ISP.PCTFREE$
, ISP.INITRANS
, ISP.MAXTRANS
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.INITIAL_STG * TS.BLOCKSIZE
, S.INIEXTS * TS.BLOCKSIZE)
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.NEXT_STG * TS.BLOCKSIZE
, S.EXTSIZE * TS.BLOCKSIZE)
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.MINEXT_STG
, S.MINEXTS)
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.MAXEXT_STG
, S.MAXEXTS)
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.MAXSIZ_STG * TS.BLOCKSIZE
, DECODE(BITAND(S.SPARE1
, 4194304)
, 4194304
, BITMAPRANGES
, NULL))
, DECODE(BITAND(TS.FLAGS
, 3)
, 1
, TO_NUMBER(NULL)
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.PCTINC_STG
, S.EXTPCT))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, 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(ISP.FLAGS
, 65536)
, 65536
, DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
, DECODE(S.GROUPS
, 0
, 1
, S.GROUPS)))
, DECODE(MOD(TRUNC(ISP.FLAGS / 4)
, 2)
, 0
, 'YES'
, 'NO')
, CASE WHEN BITAND(ISP.FLAGS
, 1024) = 1024 THEN 'ENABLED' ELSE CASE WHEN (BITAND(ISP.FLAGS
, 65536) = 65536) THEN DECODE(BITAND(DS.FLAGS_STG
, 4)
, 4
, DECODE(BITAND(DS.CMPFLAG_STG
, 6)
, 4
, 'ADVANCED LOW'
, 2
, 'ADVANCED HIGH'
, NULL)
, 'DISABLED') ELSE DECODE(BITAND(S.SPARE1
, 2048)
, 2048
, DECODE(BITAND(S.SPARE1
, 16777216 + 1048576)
, 16777216
, 'ADVANCED HIGH'
, 1048576
, 'ADVANCED LOW'
, NULL)
, 'DISABLED') END END
, ISP.BLEVEL
, ISP.LEAFCNT
, ISP.DISTKEY
, ISP.LBLKKEY
, ISP.DBLKKEY
, ISP.CLUFAC
, ISP.ROWCNT
, ISP.SAMPLESIZE
, ISP.ANALYZETIME
, DECODE(BITAND(DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(ISP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(ISP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(ISP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, DECODE(BITAND(ISP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES') FROM SYS.OBJ$ SO
, SYS.OBJ$ PO
, SYS.INDCOMPART$ ICP
, SYS.INDSUBPART$ ISP
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.USER$ U
, SYS.IND$ I
, SYS.TAB$ T
, SYS.DEFERRED_STG$ DS WHERE SO.OBJ# = ISP.OBJ#
AND PO.OBJ# = ICP.OBJ# AND ICP.OBJ# = ISP.POBJ#
AND ISP.TS# = TS.TS#
AND U.USER# = PO.OWNER# AND ISP.FILE# = S.FILE#(+)
AND ISP.BLOCK# = S.BLOCK#(+) AND ISP.TS# = S.TS#(+)
AND ISP.OBJ# = DS.OBJ#(+) AND ICP.BO# = I.OBJ#
AND I.BO# = T.OBJ# AND BITAND(ICP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ BITAND(ISP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ BITAND(T.TRIGFLAG
, 1073741824) != 1073741824
AND PO.NAMESPACE = 4
AND PO.REMOTEOWNER IS NULL
AND PO.LINKNAME IS NULL
AND SO.NAMESPACE = 4
AND SO.REMOTEOWNER IS NULL
AND SO.LINKNAME IS NULL