Database Reference
In-Depth Information
SQL> CREATE TABLE blog (
username VARCHAR2(30),
date_time DATE,
text CLOB,
img BLOB)
LOB (text) STORE AS blog_text_clob (RETENTION),
LOB (img) STORE AS blog_img_blob (PCTVERSION 10);
Table created.
SQL> SELECT pctversion, retention FROM user_lobs WHERE table_name='BLOG';
PCTVERSION RETENTION
---------- ----------
10 10800
10 10800
SQL> SHOW PARAMETER undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
undo_retention integer 10800
The result of querying the data dictionary view
USER_LOBS
is obviously incorrect. Looking
at
sql.bsq
, there's unfortunately no comment that says which column is used to discern
PCTVERSION
and
RETENTION
, though it appears likely that the column
FLAGS
holds the required
information. Here's the relevant excerpt of
sql.bsq
:
create table lob$ /* LOB information table */
( obj# number not null, /* object number of the base table */
…
lobj# number not null, /* object number for the LOB */
…
pctversion$ number not null, /* version pool */
flags number not null, /* 0x0000 = CACHE */
/* 0x0001 = NOCACHE LOGGING */
/* 0x0002 = NOCACHE NOLOGGING */
…
retention number not null, /* retention value = UNDO_RETENTION */
The
PCTVERSION
setting is stored in the column
PCTVERSION$
and the undo retention setting
is stored in the column
RETENTION
. Since
LOB$.LOBJ#
corresponds to
DBA_OBJECTS.OBJECT_ID
(see definition of
DBA_LOBS
in the file
catalog.sql
), we can query
LOB$.FLAGS
for our table by
joining
DBA_OBJECTS
and
LOB$
:
SQL> SELECT object_name, flags
FROM sys.lob$ l, dba_objects o
WHERE l.lobj#=o.object_id
AND o.object_name IN ('BLOG_TEXT_CLOB', 'BLOG_IMG_BLOB');
OBJECT_NAME FLAGS
-------------------- ----------
BLOG_IMG_BLOB 65
BLOG_TEXT_CLOB 97
