VIEW: SYS.DBA_LOBS

Source

View Text - Preformatted

select u.name, o.name,
       decode(bitand(c.property, 1), 1, ac.name, c.name), lo.name,
       decode(bitand(l.property, 8),
           8, decode(l.ts#, 2147483647, ts1.name, ts.name), ts.name),
       io.name,
       l.chunk * decode(bitand(l.property, 8), 8, ts1.blocksize,
                        ts.blocksize),
       decode(bitand(l.flags, 32), 0, l.pctversion$, to_number(NULL)),
       decode(bitand(l.flags, 32), 32,
              decode(bitand(l.property, 2048), 2048, to_number(NULL),
                     l.retention), to_number(NULL)),
       decode(l.freepools, 0, to_number(NULL), 65534, to_number(NULL),
              65535, to_number(NULL), l.freepools),
       decode(bitand(l.flags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                   16, 'CACHEREADS', 256, 'YES',
                                   512, 'YES', 'YES'),
       decode(bitand(l.flags, 786), 2, 'NO', 16, 'NO', 256, 'NO', 512,
                                       'YES', 'YES'),
       decode(bitand(l.flags, 4096), 4096, 'YES',
              decode(bitand(l.property, 2048), 2048, 'NO', 'NONE')),
       decode(bitand(l.flags, 57344), 8192, 'LOW', 16384, 'MEDIUM', 32768,
              'HIGH',
              decode(bitand(l.property, 2048), 2048, 'NO', 'NONE')),
       decode(bitand(l.flags, 458752), 65536, 'LOB', 131072, 'OBJECT',
              327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE',
              decode(bitand(l.property, 2048), 2048, 'NO', 'NONE')),
       decode(bitand(l.property, 2), 2, 'YES', 'NO'),
       decode(c.type#, 113, 'NOT APPLICABLE ',
              decode(bitand(l.property, 512), 512,
                     'ENDIAN SPECIFIC', 'ENDIAN NEUTRAL ')),
       decode(bitand(ta.property, 32), 32, 'YES', 'NO'),
       decode(bitand(l.property, 2048), 2048, 'YES', 'NO'),
       decode(bitand(l.property, 4096), 4096, 'NO',
              decode(bitand(ta.property, 32), 32, 'N/A', 'YES')),
       decode (bitand(l.property, 2048),
               2048,
               decode(bitand(ta.property, 17179869184), 17179869184,
                      decode(ds.lobret_stg, to_number(NULL), 'DEFAULT',
                                            0, 'NONE', 1, 'AUTO',
                                            2, 'MIN', 3, 'MAX',
                                            4, 'DEFAULT', 'INVALID'),
                      decode(s.lists, 0, 'NONE', 1, 'AUTO',
                                      2, 'MIN', 3, 'MAX',
                                      4, 'DEFAULT', 'INVALID')),
               decode(bitand(l.flags, 32), 32, 'YES', 'NO')),
       decode (bitand(l.property, 2048),
               2048,
               decode(bitand(ta.property, 17179869184), 17179869184,
                      decode(ds.lobret_stg, 2, ds.mintim_stg, to_number(NULL)),
                      decode(s.lists, 2, s.groups, to_number(NULL))))
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac, sys.tab$ ta, sys.lob$ l,
     sys.obj$ lo, sys.obj$ io, sys.user$ u, sys.ts$ ts, sys.ts$ ts1,
     sys.seg$ s, sys.deferred_stg$ ds
where o.owner# = u.user#
  and bitand(o.flags, 128) = 0
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.ind# = io.obj#
  and l.ts# = ts.ts#(+)
  and u.tempts# = ts1.ts#
  and c.obj# = ac.obj#(+)
  and c.intcol# = ac.intcol#(+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and o.obj# = ta.obj#
  and bitand(ta.property, 32) != 32           /* not partitioned table */
  and l.file# = s.file#(+)
  and l.block# = s.block#(+)
  and l.ts# = s.ts#(+)
  and l.lobj# = ds.obj#(+)
union all
select u.name, o.name,
       decode(bitand(c.property, 1), 1, ac.name, c.name),
       lo.name,
       NVL(ts1.name,
        (select ts2.name
        from    ts$ ts2, partobj$ po
        where   o.obj# = po.obj# and po.defts# = ts2.ts#)),
       io.name,
       plob.defchunk * NVL(ts1.blocksize, NVL((
        select ts2.blocksize
        from   sys.ts$ ts2, sys.lobfrag$ lf
        where  l.lobj# = lf.parentobj# and
               lf.ts# = ts2.ts# and rownum < 2),
        (select ts2.blocksize
        from   sys.ts$ ts2, sys.lobcomppart$ lcp, sys.lobfrag$ lf
        where  l.lobj# = lcp.lobj# and lcp.partobj# = lf.parentobj# and
               lf.ts# = ts2.ts# and rownum < 2))),
       decode(bitand(l.flags, 32), 0, plob.defpctver$, to_number(NULL)),
       decode(bitand(l.flags, 32), 32, l.retention, to_number(NULL)),
       decode(l.freepools, 0, to_number(NULL), 65534, to_number(NULL),
              65535, to_number(NULL), l.freepools),
       decode(bitand(plob.defflags, 795), 1, 'NO', 2, 'NO', 8, 'CACHEREADS',
                                         16, 'CACHEREADS', 256, 'YES',
                                         512, 'YES',  'YES'),
       decode(bitand(plob.defflags, 790), 0,'NONE', 4,'YES', 2,'NO',
                                        16,'NO', 256, 'NO',
                                        512, 'YES', 'UNKNOWN'),
       decode(bitand(plob.defflags, 4096), 4096, 'YES',
              decode(bitand(plob.defpro, 2048), 2048, 'NO', 'NONE')),
       decode(bitand(plob.defflags, 57344), 8192, 'LOW', 16384, 'MEDIUM',
              32768, 'HIGH',
              decode(bitand(plob.defpro, 2048), 2048, 'NO', 'NONE')),
       decode(bitand(plob.defflags, 458752), 65536, 'LOB', 131072, 'OBJECT',
              327680, 'LOB VALIDATE', 393216, 'OBJECT VALIDATE',
              decode(bitand(plob.defpro, 2048), 2048, 'NO', 'NONE')),
       decode(bitand(plob.defpro, 2), 2, 'YES', 'NO'),
       decode(c.type#, 113, 'NOT APPLICABLE ',
              decode(bitand(l.property, 512), 512,
                     'ENDIAN SPECIFIC', 'ENDIAN NEUTRAL ')),
       decode(bitand(ta.property, 32), 32, 'YES', 'NO'),
       decode(bitand(plob.defpro, 2048), 2048, 'YES', 'NO'),
       decode(bitand(l.property, 4096), 4096, 'NO',
              decode(bitand(ta.property, 32), 32, 'N/A', 'YES')),
       decode (bitand(plob.defpro, 2048), 2048,
               decode(bitand(ta.property, 17179869184), 17179869184,
                      decode(ds.lobret_stg, to_number(NULL), 'DEFAULT',
                                            0, 'NONE', 1, 'AUTO',
                                            2, 'MIN', 3, 'MAX',
                                            4, 'DEFAULT', 'INVALID'),
                      decode(s.lists, to_number(NULL), 'DEFAULT',
                                      0, 'NONE', 1, 'AUTO',
                                      2, 'MIN', 3, 'MAX',
                                      4, 'DEFAULT', 'INVALID')),
               decode(bitand(plob.defflags, 32), 32, 'YES', 'NO')),
       decode (bitand(plob.defpro, 2048),
               2048, decode(bitand(ta.property, 17179869184), 17179869184,
                            decode(ds.lobret_stg, 2, plob.defmintime,
                                   to_number(NULL)),
                            decode(s.lists, 2, plob.defmintime, to_number(NULL))
                           ))
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac, sys.partlob$ plob,
     sys.lob$ l, sys.obj$ lo, sys.obj$ io, sys.ts$ ts1, sys.tab$ ta,
     sys.user$ u, sys.seg$ s, sys.deferred_stg$ ds
where o.owner# = u.user#
  and o.obj# = c.obj#
  and c.obj# = l.obj#
  and c.intcol# = l.intcol#
  and l.lobj# = lo.obj#
  and l.ind# = io.obj#
  and l.lobj# = plob.lobj#
  and plob.defts# = ts1.ts# (+)
  and c.obj# = ac.obj#(+)
  and c.intcol# = ac.intcol#(+)
  and bitand(c.property,32768) != 32768           /* not unused column */
  and o.obj# = ta.obj#
  and bitand(ta.property, 32) = 32                /* partitioned table */
  and l.file# = s.file#(+)
  and l.block# = s.block#(+)
  and l.ts# = s.ts#(+)
  and l.lobj# = ds.obj#(+)

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, AC.NAME
, C.NAME)
, LO.NAME
, DECODE(BITAND(L.PROPERTY
, 8)
, 8
, DECODE(L.TS#
, 2147483647
, TS1.NAME
, TS.NAME)
, TS.NAME)
, IO.NAME
, L.CHUNK * DECODE(BITAND(L.PROPERTY
, 8)
, 8
, TS1.BLOCKSIZE
, TS.BLOCKSIZE)
, DECODE(BITAND(L.FLAGS
, 32)
, 0
, L.PCTVERSION$
, TO_NUMBER(NULL))
, DECODE(BITAND(L.FLAGS
, 32)
, 32
, DECODE(BITAND(L.PROPERTY
, 2048)
, 2048
, TO_NUMBER(NULL)
, L.RETENTION)
, TO_NUMBER(NULL))
, DECODE(L.FREEPOOLS
, 0
, TO_NUMBER(NULL)
, 65534
, TO_NUMBER(NULL)
, 65535
, TO_NUMBER(NULL)
, L.FREEPOOLS)
, DECODE(BITAND(L.FLAGS
, 795)
, 1
, 'NO'
, 2
, 'NO'
, 8
, 'CACHEREADS'
, 16
, 'CACHEREADS'
, 256
, 'YES'
, 512
, 'YES'
, 'YES')
, DECODE(BITAND(L.FLAGS
, 786)
, 2
, 'NO'
, 16
, 'NO'
, 256
, 'NO'
, 512
, 'YES'
, 'YES')
, DECODE(BITAND(L.FLAGS
, 4096)
, 4096
, 'YES'
, DECODE(BITAND(L.PROPERTY
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(L.FLAGS
, 57344)
, 8192
, 'LOW'
, 16384
, 'MEDIUM'
, 32768
, 'HIGH'
, DECODE(BITAND(L.PROPERTY
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(L.FLAGS
, 458752)
, 65536
, 'LOB'
, 131072
, 'OBJECT'
, 327680
, 'LOB VALIDATE'
, 393216
, 'OBJECT VALIDATE'
, DECODE(BITAND(L.PROPERTY
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(L.PROPERTY
, 2)
, 2
, 'YES'
, 'NO')
, DECODE(C.TYPE#
, 113
, 'NOT APPLICABLE '
, DECODE(BITAND(L.PROPERTY
, 512)
, 512
, 'ENDIAN SPECIFIC'
, 'ENDIAN NEUTRAL '))
, DECODE(BITAND(TA.PROPERTY
, 32)
, 32
, 'YES'
, 'NO')
, DECODE(BITAND(L.PROPERTY
, 2048)
, 2048
, 'YES'
, 'NO')
, DECODE(BITAND(L.PROPERTY
, 4096)
, 4096
, 'NO'
, DECODE(BITAND(TA.PROPERTY
, 32)
, 32
, 'N/A'
, 'YES'))
, DECODE (BITAND(L.PROPERTY
, 2048)
, 2048
, DECODE(BITAND(TA.PROPERTY
, 17179869184)
, 17179869184
, DECODE(DS.LOBRET_STG
, TO_NUMBER(NULL)
, 'DEFAULT'
, 0
, 'NONE'
, 1
, 'AUTO'
, 2
, 'MIN'
, 3
, 'MAX'
, 4
, 'DEFAULT'
, 'INVALID')
, DECODE(S.LISTS
, 0
, 'NONE'
, 1
, 'AUTO'
, 2
, 'MIN'
, 3
, 'MAX'
, 4
, 'DEFAULT'
, 'INVALID'))
, DECODE(BITAND(L.FLAGS
, 32)
, 32
, 'YES'
, 'NO'))
, DECODE (BITAND(L.PROPERTY
, 2048)
, 2048
, DECODE(BITAND(TA.PROPERTY
, 17179869184)
, 17179869184
, DECODE(DS.LOBRET_STG
, 2
, DS.MINTIM_STG
, TO_NUMBER(NULL))
, DECODE(S.LISTS
, 2
, S.GROUPS
, TO_NUMBER(NULL)))) FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.ATTRCOL$ AC
, SYS.TAB$ TA
, SYS.LOB$ L
, SYS.OBJ$ LO
, SYS.OBJ$ IO
, SYS.USER$ U
, SYS.TS$ TS
, SYS.TS$ TS1
, SYS.SEG$ S
, SYS.DEFERRED_STG$ DS WHERE O.OWNER# = U.USER#
AND BITAND(O.FLAGS
, 128) = 0
AND O.OBJ# = C.OBJ#
AND C.OBJ# = L.OBJ#
AND C.INTCOL# = L.INTCOL#
AND L.LOBJ# = LO.OBJ#
AND L.IND# = IO.OBJ#
AND L.TS# = TS.TS#(+)
AND U.TEMPTS# = TS1.TS#
AND C.OBJ# = AC.OBJ#(+)
AND C.INTCOL# = AC.INTCOL#(+)
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
AND O.OBJ# = TA.OBJ#
AND BITAND(TA.PROPERTY
, 32) != 32 /* NOT PARTITIONED TABLE */
AND L.FILE# = S.FILE#(+)
AND L.BLOCK# = S.BLOCK#(+)
AND L.TS# = S.TS#(+)
AND L.LOBJ# = DS.OBJ#(+) UNION ALL SELECT U.NAME
, O.NAME
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, AC.NAME
, C.NAME)
, LO.NAME
, NVL(TS1.NAME
, (SELECT TS2.NAME
FROM TS$ TS2
, PARTOBJ$ PO
WHERE O.OBJ# = PO.OBJ#
AND PO.DEFTS# = TS2.TS#))
, IO.NAME
, PLOB.DEFCHUNK * NVL(TS1.BLOCKSIZE
, NVL(( SELECT TS2.BLOCKSIZE
FROM SYS.TS$ TS2
, SYS.LOBFRAG$ LF
WHERE L.LOBJ# = LF.PARENTOBJ# AND LF.TS# = TS2.TS#
AND ROWNUM < 2)
, (SELECT TS2.BLOCKSIZE
FROM SYS.TS$ TS2
, SYS.LOBCOMPPART$ LCP
, SYS.LOBFRAG$ LF
WHERE L.LOBJ# = LCP.LOBJ#
AND LCP.PARTOBJ# = LF.PARENTOBJ# AND LF.TS# = TS2.TS#
AND ROWNUM < 2)))
, DECODE(BITAND(L.FLAGS
, 32)
, 0
, PLOB.DEFPCTVER$
, TO_NUMBER(NULL))
, DECODE(BITAND(L.FLAGS
, 32)
, 32
, L.RETENTION
, TO_NUMBER(NULL))
, DECODE(L.FREEPOOLS
, 0
, TO_NUMBER(NULL)
, 65534
, TO_NUMBER(NULL)
, 65535
, TO_NUMBER(NULL)
, L.FREEPOOLS)
, DECODE(BITAND(PLOB.DEFFLAGS
, 795)
, 1
, 'NO'
, 2
, 'NO'
, 8
, 'CACHEREADS'
, 16
, 'CACHEREADS'
, 256
, 'YES'
, 512
, 'YES'
, 'YES')
, DECODE(BITAND(PLOB.DEFFLAGS
, 790)
, 0
, 'NONE'
, 4
, 'YES'
, 2
, 'NO'
, 16
, 'NO'
, 256
, 'NO'
, 512
, 'YES'
, 'UNKNOWN')
, DECODE(BITAND(PLOB.DEFFLAGS
, 4096)
, 4096
, 'YES'
, DECODE(BITAND(PLOB.DEFPRO
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(PLOB.DEFFLAGS
, 57344)
, 8192
, 'LOW'
, 16384
, 'MEDIUM'
, 32768
, 'HIGH'
, DECODE(BITAND(PLOB.DEFPRO
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(PLOB.DEFFLAGS
, 458752)
, 65536
, 'LOB'
, 131072
, 'OBJECT'
, 327680
, 'LOB VALIDATE'
, 393216
, 'OBJECT VALIDATE'
, DECODE(BITAND(PLOB.DEFPRO
, 2048)
, 2048
, 'NO'
, 'NONE'))
, DECODE(BITAND(PLOB.DEFPRO
, 2)
, 2
, 'YES'
, 'NO')
, DECODE(C.TYPE#
, 113
, 'NOT APPLICABLE '
, DECODE(BITAND(L.PROPERTY
, 512)
, 512
, 'ENDIAN SPECIFIC'
, 'ENDIAN NEUTRAL '))
, DECODE(BITAND(TA.PROPERTY
, 32)
, 32
, 'YES'
, 'NO')
, DECODE(BITAND(PLOB.DEFPRO
, 2048)
, 2048
, 'YES'
, 'NO')
, DECODE(BITAND(L.PROPERTY
, 4096)
, 4096
, 'NO'
, DECODE(BITAND(TA.PROPERTY
, 32)
, 32
, 'N/A'
, 'YES'))
, DECODE (BITAND(PLOB.DEFPRO
, 2048)
, 2048
, DECODE(BITAND(TA.PROPERTY
, 17179869184)
, 17179869184
, DECODE(DS.LOBRET_STG
, TO_NUMBER(NULL)
, 'DEFAULT'
, 0
, 'NONE'
, 1
, 'AUTO'
, 2
, 'MIN'
, 3
, 'MAX'
, 4
, 'DEFAULT'
, 'INVALID')
, DECODE(S.LISTS
, TO_NUMBER(NULL)
, 'DEFAULT'
, 0
, 'NONE'
, 1
, 'AUTO'
, 2
, 'MIN'
, 3
, 'MAX'
, 4
, 'DEFAULT'
, 'INVALID'))
, DECODE(BITAND(PLOB.DEFFLAGS
, 32)
, 32
, 'YES'
, 'NO'))
, DECODE (BITAND(PLOB.DEFPRO
, 2048)
, 2048
, DECODE(BITAND(TA.PROPERTY
, 17179869184)
, 17179869184
, DECODE(DS.LOBRET_STG
, 2
, PLOB.DEFMINTIME
, TO_NUMBER(NULL))
, DECODE(S.LISTS
, 2
, PLOB.DEFMINTIME
, TO_NUMBER(NULL)) )) FROM SYS.OBJ$ O
, SYS.COL$ C
, SYS.ATTRCOL$ AC
, SYS.PARTLOB$ PLOB
, SYS.LOB$ L
, SYS.OBJ$ LO
, SYS.OBJ$ IO
, SYS.TS$ TS1
, SYS.TAB$ TA
, SYS.USER$ U
, SYS.SEG$ S
, SYS.DEFERRED_STG$ DS WHERE O.OWNER# = U.USER#
AND O.OBJ# = C.OBJ#
AND C.OBJ# = L.OBJ#
AND C.INTCOL# = L.INTCOL#
AND L.LOBJ# = LO.OBJ#
AND L.IND# = IO.OBJ#
AND L.LOBJ# = PLOB.LOBJ#
AND PLOB.DEFTS# = TS1.TS# (+)
AND C.OBJ# = AC.OBJ#(+)
AND C.INTCOL# = AC.INTCOL#(+)
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
AND O.OBJ# = TA.OBJ#
AND BITAND(TA.PROPERTY
, 32) = 32 /* PARTITIONED TABLE */
AND L.FILE# = S.FILE#(+)
AND L.BLOCK# = S.BLOCK#(+)
AND L.TS# = S.TS#(+)
AND L.LOBJ# = DS.OBJ#(+)