BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Redneck

DBA_LOBS view tips

Oracle Tips by Burleson Consulting

DBA_LOBS

<< Return to the index

Oracle 11g makes the following comments about the DBA_LOBS table: "Description of LOBs contained in all tables"

Related notes on DBA_LOBS:

Column description of the DBA_LOBS view:

OWNER

Description of DBA_LOBS.OWNER: "Owner of the table containing the LOB"

TABLE_NAME

Description of DBA_LOBS.TABLE_NAME: "Name of the table containing the LOB"

COLUMN_NAME

Description of DBA_LOBS.COLUMN_NAME: "Name of the LOB column or attribute"

SEGMENT_NAME

Description of DBA_LOBS.SEGMENT_NAME: "Name of the LOB segment"

TABLESPACE_NAME

Description of DBA_LOBS.TABLESPACE_NAME: "Name of the tablespace containing the LOB segment"

INDEX_NAME

Description of DBA_LOBS.INDEX_NAME: "Name of the LOB index"

CHUNK

Description of DBA_LOBS.CHUNK: "Size of the LOB chunk as a unit of allocation/manipulation in bytes"

PCTVERSION

Description of DBA_LOBS.PCTVERSION: "Maximum percentage of the LOB space used for versioning"

RETENTION

Description of DBA_LOBS.RETENTION: "Maximum time duration for versioning of the LOB space"

FREEPOOLS

Description of DBA_LOBS.FREEPOOLS: "Number of freepools for this LOB segment"

CACHE

Description of DBA_LOBS.CACHE: "Is the LOB accessed through the buffer cache?"

LOGGING

Description of DBA_LOBS.LOGGING: "Are changes to the LOB logged?"

ENCRYPT

Description of DBA_LOBS.ENCRYPT: "Is this lob encrypted?"

COMPRESSION

Description of DBA_LOBS.COMPRESSION: "What level of compression is used for this lob?"

DEDUPLICATION

Description of DBA_LOBS.DEDUPLICATION: "What kind of deduplication is used for this lob?"

IN_ROW

Description of DBA_LOBS.IN_ROW: "Are some of the LOBs stored with the base row?"

FORMAT

Description of DBA_LOBS.FORMAT: "Is the LOB storage format dependent on the endianness of the platform?"

PARTITIONED

Description of DBA_LOBS.PARTITIONED: "Is the LOB column in a partitioned table?"

SECUREFILE

Description of DBA_LOBS.SECUREFILE: "Is the LOB a SECUREFILE LOB?"

DBA_LOBS View Source

Oracle 11g's data dictionary defines the DBA_LOBS view using the following source query:

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, 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')
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
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 */
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')
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
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 */

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books