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.

[Home] [Help]

VIEW: SYS.ALL_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   obj$ so, sys.obj$ po, ind$ i, indcompart$ icp, indsubpart$ isp,
       ts$ ts, seg$ s, user$ u, tab$ t, sys.deferred_stg$ ds
where  so.obj# = isp.obj# and po.obj# = icp.obj# and icp.obj# = isp.pobj# and
       i.obj# = icp.bo# and ts.ts# = isp.ts# and isp.file# = s.file#(+) and
       isp.block# = s.block#(+) and isp.ts# = s.ts#(+) and
       isp.obj# = ds.obj#(+) and u.user# = po.owner# and
       bitand(icp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       bitand(isp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and
       i.type# != 8 and      /* not LOB index */
       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 and
       ((po.owner# = userenv('SCHEMAID') and so.owner# = userenv('SCHEMAID'))
        or i.bo# 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
, 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 OBJ$ SO
, SYS.OBJ$ PO
, IND$ I
, INDCOMPART$ ICP
, INDSUBPART$ ISP
, TS$ TS
, SEG$ S
, USER$ U
, TAB$ T
, SYS.DEFERRED_STG$ DS WHERE SO.OBJ# = ISP.OBJ#
AND PO.OBJ# = ICP.OBJ#
AND ICP.OBJ# = ISP.POBJ# AND I.OBJ# = ICP.BO#
AND TS.TS# = ISP.TS#
AND ISP.FILE# = S.FILE#(+) AND ISP.BLOCK# = S.BLOCK#(+)
AND ISP.TS# = S.TS#(+) AND ISP.OBJ# = DS.OBJ#(+)
AND U.USER# = PO.OWNER# AND BITAND(ICP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ BITAND(ISP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ I.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND I.TYPE# != 8
AND /* NOT LOB INDEX */ 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 AND ((PO.OWNER# = USERENV('SCHEMAID')
AND SO.OWNER# = USERENV('SCHEMAID')) OR I.BO# 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 */) ) )