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_PARTITIONS

Source

View Text - Preformatted


select u.name, io.name, 'NO', io.subname, 0,
       ip.hiboundval, ip.hiboundlen,
       row_number() over (partition by u.name, io.name order by ip.part#),
       decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'), ts.name,
       ip.pctfree$,ip.initrans, ip.maxtrans,
       decode(bitand(ip.flags, 65536), 65536,
              ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize),
       decode(bitand(ip.flags, 65536), 65536,
              ds.next_stg * ts.blocksize, s.extsize * ts.blocksize),
       decode(bitand(ip.flags, 65536), 65536, ds.minext_stg, s.minexts),
       decode(bitand(ip.flags, 65536), 65536, ds.maxext_stg, s.maxexts),
       decode(bitand(ip.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(ip.flags, 65536), 65536, ds.pctinc_stg, s.extpct)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(bitand(ip.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(ip.flags, 65536), 65536,
                     decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                     decode(s.groups, 0, 1, s.groups))),
       decode(mod(trunc(ip.flags / 4), 2), 0, 'YES', 'NO'),
       case
         when bitand(ip.flags, 1024) = 1024 then 'ENABLED'
       else
         case when (bitand(ip.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,
       ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey,
       ip.clufac, ip.rowcnt, ip.samplesize, ip.analyzetime,
       decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3),
              1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$,
       decode(bitand(ip.flags, 16), 0, 'NO', 'YES'),'','',
       decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'),
       decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES'),
       decode(bitand(ip.flags, 262144), 262144, 'YES', 'NO')
from   obj$ io, indpart$ ip, ts$ ts, sys.seg$ s, user$ u, ind$ i, tab$ t,
       sys.deferred_stg$ ds
where  io.obj# = ip.obj# and ts.ts# = ip.ts# and ip.file#=s.file#(+) and
       ip.block#=s.block#(+) and ip.ts#=s.ts#(+) and io.owner# = u.user# and
       i.obj# = ip.bo# and i.bo# = t.obj# and ip.obj# = ds.obj#(+) and
       i.type# != 9 and
       bitand(ip.flags, 8388608) = 0 and   /* filter out hidden partitions */
       bitand(t.trigflag, 1073741824) != 1073741824
       and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL
      union all
select u.name, io.name, 'YES', io.subname, icp.subpartcnt,
       icp.hiboundval, icp.hiboundlen,
       row_number() over (partition by u.name, io.name order by icp.part#),
       'N/A', ts.name,
       icp.defpctfree, icp.definitrans, icp.defmaxtrans,
       icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts,
       icp.defmaxsize, icp.defextpct, icp.deflists, icp.defgroups,
       decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       case
         when bitand(icp.spare3, 8) = 8 then 'ADVANCED LOW'
         when bitand(icp.spare3, 4) = 4 then 'ADVANCED HIGH'
       else
         decode(bitand(icp.flags, 1024), 1024, 'ENABLED', 'DISABLED')
       end,
       icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey,
       icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime,
       decode(bitand(icp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(icp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(icp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL),
       decode(bitand(icp.flags, 16), 0, 'NO', 'YES'),'','',
       decode(bitand(icp.flags, 32768), 32768, 'YES', 'NO'), 'N/A', 'N/A'
from   obj$ io, indcompart$ icp, ts$ ts, user$ u, ind$ i, tab$ t
where  io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and
       u.user# = io.owner# and i.obj# = icp.bo# and i.bo# = t.obj# and
       i.type# != 9 and
       bitand(t.trigflag, 1073741824) != 1073741824 and
       bitand(icp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL
      union all
select u.name, io.name, 'NO', io.subname, 0,
       ip.hiboundval, ip.hiboundlen,
       row_number() over (partition by u.name, io.name order by ip.part#),
       decode(bitand(ip.flags, 1), 1, 'UNUSABLE',
                decode(bitand(ip.flags, 4096), 4096, 'INPROGRS', 'USABLE')),
       null, ip.pctfree$, ip.initrans, ip.maxtrans,
       0, 0, 0, 0, 0, 0, 0, 0,
       decode(mod(trunc(ip.flags / 4), 2), 0, 'YES', 'NO'),
       'N/A',
       ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey,
       ip.clufac, ip.rowcnt, ip.samplesize, ip.analyzetime,
       'DEFAULT', 'DEFAULT', 'DEFAULT',
       decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$,
       decode(bitand(ip.flags, 16), 0, 'NO', 'YES'),
       decode(i.type#,
             9, decode(bitand(ip.flags, 8192), 8192, 'FAILED', 'VALID'),
             ''),
       ipp.parameters,
       decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'),
       decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES'),
       decode(bitand(ip.flags, 262144), 262144, 'YES', 'NO')
from   obj$ io, indpart$ ip,  user$ u, ind$ i, indpart_param$ ipp, tab$ t
where  io.obj# = ip.obj# and io.owner# = u.user# and
       ip.bo# = i.obj# and ip.obj# = ipp.obj# and i.bo# = t.obj# and
       bitand(t.trigflag, 1073741824) != 1073741824
       and io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL

View Text - HTML Formatted

SELECT U.NAME
, IO.NAME
, 'NO'
, IO.SUBNAME
, 0
, IP.HIBOUNDVAL
, IP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, IO.NAME ORDER BY IP.PART#)
, DECODE(BITAND(IP.FLAGS
, 1)
, 1
, 'UNUSABLE'
, 'USABLE')
, TS.NAME
, IP.PCTFREE$
, IP.INITRANS
, IP.MAXTRANS
, DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.INITIAL_STG * TS.BLOCKSIZE
, S.INIEXTS * TS.BLOCKSIZE)
, DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.NEXT_STG * TS.BLOCKSIZE
, S.EXTSIZE * TS.BLOCKSIZE)
, DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.MINEXT_STG
, S.MINEXTS)
, DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.MAXEXT_STG
, S.MAXEXTS)
, DECODE(BITAND(IP.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(IP.FLAGS
, 65536)
, 65536
, DS.PCTINC_STG
, S.EXTPCT))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(IP.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(IP.FLAGS
, 65536)
, 65536
, DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
, DECODE(S.GROUPS
, 0
, 1
, S.GROUPS)))
, DECODE(MOD(TRUNC(IP.FLAGS / 4)
, 2)
, 0
, 'YES'
, 'NO')
, CASE WHEN BITAND(IP.FLAGS
, 1024) = 1024 THEN 'ENABLED' ELSE CASE WHEN (BITAND(IP.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
, IP.BLEVEL
, IP.LEAFCNT
, IP.DISTKEY
, IP.LBLKKEY
, IP.DBLKKEY
, IP.CLUFAC
, IP.ROWCNT
, IP.SAMPLESIZE
, IP.ANALYZETIME
, DECODE(BITAND(DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(IP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, IP.PCTTHRES$
, DECODE(BITAND(IP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, ''
, ''
, DECODE(BITAND(IP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES')
, DECODE(BITAND(IP.FLAGS
, 262144)
, 262144
, 'YES'
, 'NO') FROM OBJ$ IO
, INDPART$ IP
, TS$ TS
, SYS.SEG$ S
, USER$ U
, IND$ I
, TAB$ T
, SYS.DEFERRED_STG$ DS WHERE IO.OBJ# = IP.OBJ#
AND TS.TS# = IP.TS#
AND IP.FILE#=S.FILE#(+) AND IP.BLOCK#=S.BLOCK#(+)
AND IP.TS#=S.TS#(+)
AND IO.OWNER# = U.USER# AND I.OBJ# = IP.BO#
AND I.BO# = T.OBJ#
AND IP.OBJ# = DS.OBJ#(+) AND I.TYPE# != 9 AND BITAND(IP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ BITAND(T.TRIGFLAG
, 1073741824) != 1073741824
AND IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL UNION ALL SELECT U.NAME
, IO.NAME
, 'YES'
, IO.SUBNAME
, ICP.SUBPARTCNT
, ICP.HIBOUNDVAL
, ICP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, IO.NAME ORDER BY ICP.PART#)
, 'N/A'
, TS.NAME
, ICP.DEFPCTFREE
, ICP.DEFINITRANS
, ICP.DEFMAXTRANS
, ICP.DEFINIEXTS
, ICP.DEFEXTSIZE
, ICP.DEFMINEXTS
, ICP.DEFMAXEXTS
, ICP.DEFMAXSIZE
, ICP.DEFEXTPCT
, ICP.DEFLISTS
, ICP.DEFGROUPS
, DECODE(ICP.DEFLOGGING
, 0
, 'NONE'
, 1
, 'YES'
, 2
, 'NO'
, 'UNKNOWN')
, CASE WHEN BITAND(ICP.SPARE3
, 8) = 8 THEN 'ADVANCED LOW' WHEN BITAND(ICP.SPARE3
, 4) = 4 THEN 'ADVANCED HIGH' ELSE DECODE(BITAND(ICP.FLAGS
, 1024)
, 1024
, 'ENABLED'
, 'DISABLED') END
, ICP.BLEVEL
, ICP.LEAFCNT
, ICP.DISTKEY
, ICP.LBLKKEY
, ICP.DBLKKEY
, ICP.CLUFAC
, ICP.ROWCNT
, ICP.SAMPLESIZE
, ICP.ANALYZETIME
, DECODE(BITAND(ICP.DEFBUFPOOL
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(ICP.DEFBUFPOOL
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(ICP.DEFBUFPOOL
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(ICP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, TO_NUMBER(NULL)
, DECODE(BITAND(ICP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, ''
, ''
, DECODE(BITAND(ICP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, 'N/A'
, 'N/A' FROM OBJ$ IO
, INDCOMPART$ ICP
, TS$ TS
, USER$ U
, IND$ I
, TAB$ T WHERE IO.OBJ# = ICP.OBJ#
AND ICP.DEFTS# = TS.TS# (+) AND U.USER# = IO.OWNER#
AND I.OBJ# = ICP.BO#
AND I.BO# = T.OBJ# AND I.TYPE# != 9 AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND BITAND(ICP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL UNION ALL SELECT U.NAME
, IO.NAME
, 'NO'
, IO.SUBNAME
, 0
, IP.HIBOUNDVAL
, IP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, IO.NAME ORDER BY IP.PART#)
, DECODE(BITAND(IP.FLAGS
, 1)
, 1
, 'UNUSABLE'
, DECODE(BITAND(IP.FLAGS
, 4096)
, 4096
, 'INPROGRS'
, 'USABLE'))
, NULL
, IP.PCTFREE$
, IP.INITRANS
, IP.MAXTRANS
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, DECODE(MOD(TRUNC(IP.FLAGS / 4)
, 2)
, 0
, 'YES'
, 'NO')
, 'N/A'
, IP.BLEVEL
, IP.LEAFCNT
, IP.DISTKEY
, IP.LBLKKEY
, IP.DBLKKEY
, IP.CLUFAC
, IP.ROWCNT
, IP.SAMPLESIZE
, IP.ANALYZETIME
, 'DEFAULT'
, 'DEFAULT'
, 'DEFAULT'
, DECODE(BITAND(IP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, IP.PCTTHRES$
, DECODE(BITAND(IP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(I.TYPE#
, 9
, DECODE(BITAND(IP.FLAGS
, 8192)
, 8192
, 'FAILED'
, 'VALID')
, '')
, IPP.PARAMETERS
, DECODE(BITAND(IP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES')
, DECODE(BITAND(IP.FLAGS
, 262144)
, 262144
, 'YES'
, 'NO') FROM OBJ$ IO
, INDPART$ IP
, USER$ U
, IND$ I
, INDPART_PARAM$ IPP
, TAB$ T WHERE IO.OBJ# = IP.OBJ#
AND IO.OWNER# = U.USER# AND IP.BO# = I.OBJ#
AND IP.OBJ# = IPP.OBJ#
AND I.BO# = T.OBJ# AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824
AND IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL