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_LOB_SUBPARTITIONS

Source

View Text - Preformatted


select u.name,
       o.name,
       decode(bitand(c.property, 1), 1, a.name, c.name),
       lo.name,
       lpo.subname,
       spo.subname,
       lspo.subname,
       lispo.subname,
       row_number() over (partition by u.name, o.name, lo.name, lpo.subname
                          order by lf.frag#),
       lf.chunk * ts.blocksize,
       decode(bitand(lf.fragflags, 32), 0, lf.pctversion$, to_number(NULL)),
       decode(bitand(lf.fragflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                         16, 'CACHEREADS', 256, 'YES', 512,
                                         'YES', 'YES'),
       decode(lf.fragpro, 0, 'NO', 2048, 'NO', 'YES'),
       ts.name,
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                      ds.initial_stg, s.iniexts) * ts.blocksize),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                      ds.next_stg, s.extsize) * ts.blocksize),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                      ds.minext_stg, s.minexts)),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                      ds.maxext_stg, s.maxexts)),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                      ds.maxsiz_stg * ts.blocksize,
                      decode(bitand(s.spare1, 4194304), 4194304,
                             bitmapranges, NULL))),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                     decode(bitand(lf.fragpro, 2048),
                            2048, decode(ds.lobret_stg,
                                         to_number(NULL), 'DEFAULT',
                                         0, 'NONE', 1, 'AUTO',
                                         2, 'MIN',  3, 'MAX',
                                         4, 'DEFAULT', 'INVALID'),
                            decode(bitand(lf.fragflags, 32), 32,'YES', 'NO')),
                     decode(bitand(s.spare1, 2097152), 2097152,
                            decode(s.lists, 0, 'NONE', 1, 'AUTO',
                                            2, 'MIN', 3, 'MAX',
                                            4, 'DEFAULT', 'INVALID'),
                            decode(bitand(lf.fragflags, 32), 32,'YES', 'NO')))),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                      decode(bitand(lf.fragpro, 2048), 2048,
                             ds.mintim_stg, NULL),
                      decode(bitand(s.spare1, 2097152), 2097152,
                             s.groups, NULL))),
       to_char(decode(bitand(ts.flags, 3), 1, to_number(NULL),
                     decode(bitand(lf.fragflags, 33554432), 33554432,
                           ds.pctinc_stg, s.extpct))),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                     decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
                     decode(s.lists, 0, 1, s.lists))),
       to_char(decode(bitand(lf.fragflags, 33554432), 33554432,
                     decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                     decode(s.groups, 0, 1, s.groups))),
       decode(bitand(lf.fragflags, 786), 2, 'NO', 16, 'NO', 256, 'NO',
                                         512, 'YES', 'YES'),
       decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 3),
              1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 12)/4,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(decode(bitand(lf.fragflags, 33554432), 33554432, ds.bfp_stg, s.cachehint), 48)/16,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(lf.fragflags,4096), 4096, 'YES',
              decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')),
       decode(bitand(lf.fragflags,57344), 8192, 'LOW', 16384, 'MEDIUM', 32768,
              'HIGH',
              decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')),
       decode(bitand(lf.fragflags,458752), 65536, 'LOB', 131072, 'OBJECT',
              327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE',
              decode(bitand(lf.fragpro,2048), 2048, 'NO', 'NONE')),
       decode(bitand(lf.fragpro,2048), 2048, 'YES', 'NO'),
       decode(bitand(lf.fragflags, 33554432), 33554432, 'NO', 'YES')
from   sys.obj$ o, sys.col$ c,
       sys.lob$ l, sys.obj$ lo,
       sys.lobcomppart$ lcp, sys.obj$ lpo,
       sys.lobfrag$ lf, sys.obj$ lspo,
       sys.obj$ spo, sys.obj$ lispo,
       sys.partobj$ pobj, sys.tab$ t,
       sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.attrcol$ a,
       sys.deferred_stg$ ds
where o.owner# = u.user#
  and pobj.obj# = o.obj#
  and o.obj# = t.obj#
  and bitand(t.trigflag, 1073741824) != 1073741824
  and mod(pobj.spare2, 256) != 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.lobj# = lcp.lobj#
  and lcp.partobj# = lpo.obj#
  and bitand(lcp.defflags, 67108864) = 0    /* filter out hidden fragments */
  and bitand(lf.fragflags, 67108864) = 0    /* filter out hidden fragments */
  and lf.parentobj# = lcp.partobj#
  and lf.tabfragobj# = spo.obj#
  and lf.fragobj# = lspo.obj#
  and lf.indfragobj# = lispo.obj#
  and lf.fragobj# = ds.obj#(+)
  and lf.ts# = s.ts#(+)
  and lf.file# = s.file#(+)
  and lf.block# = s.block#(+)
  and lf.ts# = ts.ts#
  and bitand(c.property,32768) != 32768           /* not unused column */
  and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
  and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
  and lo.namespace = 8 and lo.remoteowner IS NULL and lo.linkname IS NULL

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, A.NAME
, C.NAME)
, LO.NAME
, LPO.SUBNAME
, SPO.SUBNAME
, LSPO.SUBNAME
, LISPO.SUBNAME
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, O.NAME
, LO.NAME
, LPO.SUBNAME ORDER BY LF.FRAG#)
, LF.CHUNK * TS.BLOCKSIZE
, DECODE(BITAND(LF.FRAGFLAGS
, 32)
, 0
, LF.PCTVERSION$
, TO_NUMBER(NULL))
, DECODE(BITAND(LF.FRAGFLAGS
, 795)
, 1
, 'NO'
, 2
, 'NO'
, 8
, 'CACHEREADS'
, 16
, 'CACHEREADS'
, 256
, 'YES'
, 512
, 'YES'
, 'YES')
, DECODE(LF.FRAGPRO
, 0
, 'NO'
, 2048
, 'NO'
, 'YES')
, TS.NAME
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.INITIAL_STG
, S.INIEXTS) * TS.BLOCKSIZE)
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.NEXT_STG
, S.EXTSIZE) * TS.BLOCKSIZE)
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.MINEXT_STG
, S.MINEXTS))
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.MAXEXT_STG
, S.MAXEXTS))
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.MAXSIZ_STG * TS.BLOCKSIZE
, DECODE(BITAND(S.SPARE1
, 4194304)
, 4194304
, BITMAPRANGES
, NULL)))
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DECODE(BITAND(LF.FRAGPRO
, 2048)
, 2048
, DECODE(DS.LOBRET_STG
, TO_NUMBER(NULL)
, 'DEFAULT'
, 0
, 'NONE'
, 1
, 'AUTO'
, 2
, 'MIN'
, 3
, 'MAX'
, 4
, 'DEFAULT'
, 'INVALID')
, DECODE(BITAND(LF.FRAGFLAGS
, 32)
, 32
, 'YES'
, 'NO'))
, DECODE(BITAND(S.SPARE1
, 2097152)
, 2097152
, DECODE(S.LISTS
, 0
, 'NONE'
, 1
, 'AUTO'
, 2
, 'MIN'
, 3
, 'MAX'
, 4
, 'DEFAULT'
, 'INVALID')
, DECODE(BITAND(LF.FRAGFLAGS
, 32)
, 32
, 'YES'
, 'NO'))))
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DECODE(BITAND(LF.FRAGPRO
, 2048)
, 2048
, DS.MINTIM_STG
, NULL)
, DECODE(BITAND(S.SPARE1
, 2097152)
, 2097152
, S.GROUPS
, NULL)))
, TO_CHAR(DECODE(BITAND(TS.FLAGS
, 3)
, 1
, TO_NUMBER(NULL)
, DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.PCTINC_STG
, S.EXTPCT)))
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DECODE(DS.FRLINS_STG
, 0
, 1
, DS.FRLINS_STG)
, DECODE(S.LISTS
, 0
, 1
, S.LISTS)))
, TO_CHAR(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
, DECODE(S.GROUPS
, 0
, 1
, S.GROUPS)))
, DECODE(BITAND(LF.FRAGFLAGS
, 786)
, 2
, 'NO'
, 16
, 'NO'
, 256
, 'NO'
, 512
, 'YES'
, 'YES')
, DECODE(BITAND(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.BFP_STG
, S.CACHEHINT)
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(LF.FRAGFLAGS
, 4096)
, 4096
, 'YES'
, DECODE(BITAND(LF.FRAGPRO
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(LF.FRAGFLAGS
, 57344)
, 8192
, 'LOW'
, 16384
, 'MEDIUM'
, 32768
, 'HIGH'
, DECODE(BITAND(LF.FRAGPRO
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(LF.FRAGFLAGS
, 458752)
, 65536
, 'LOB'
, 131072
, 'OBJECT'
, 327680
, 'LOB VALIDATE'
, 393216
, 'OBJECT VALIDATE'
, DECODE(BITAND(LF.FRAGPRO
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(LF.FRAGPRO
, 2048)
, 2048
, 'YES'
, 'NO')
, DECODE(BITAND(LF.FRAGFLAGS
, 33554432)
, 33554432
, 'NO'
, 'YES') FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.LOB$ L
, SYS.OBJ$ LO
, SYS.LOBCOMPPART$ LCP
, SYS.OBJ$ LPO
, SYS.LOBFRAG$ LF
, SYS.OBJ$ LSPO
, SYS.OBJ$ SPO
, SYS.OBJ$ LISPO
, SYS.PARTOBJ$ POBJ
, SYS.TAB$ T
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.USER$ U
, SYS.ATTRCOL$ A
, SYS.DEFERRED_STG$ DS WHERE O.OWNER# = U.USER#
AND POBJ.OBJ# = O.OBJ#
AND O.OBJ# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824
AND MOD(POBJ.SPARE2
, 256) != 0
AND O.OBJ# = C.OBJ#
AND C.OBJ# = L.OBJ#
AND C.INTCOL# = L.INTCOL#
AND L.LOBJ# = LO.OBJ#
AND L.LOBJ# = LCP.LOBJ#
AND LCP.PARTOBJ# = LPO.OBJ#
AND BITAND(LCP.DEFFLAGS
, 67108864) = 0 /* FILTER OUT HIDDEN FRAGMENTS */
AND BITAND(LF.FRAGFLAGS
, 67108864) = 0 /* FILTER OUT HIDDEN FRAGMENTS */
AND LF.PARENTOBJ# = LCP.PARTOBJ#
AND LF.TABFRAGOBJ# = SPO.OBJ#
AND LF.FRAGOBJ# = LSPO.OBJ#
AND LF.INDFRAGOBJ# = LISPO.OBJ#
AND LF.FRAGOBJ# = DS.OBJ#(+)
AND LF.TS# = S.TS#(+)
AND LF.FILE# = S.FILE#(+)
AND LF.BLOCK# = S.BLOCK#(+)
AND LF.TS# = TS.TS#
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
AND C.OBJ# = A.OBJ#(+)
AND C.INTCOL# = A.INTCOL#(+)
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND LO.NAMESPACE = 8
AND LO.REMOTEOWNER IS NULL
AND LO.LINKNAME IS NULL