Databases Reference
In-Depth Information
There's the missing piece of information: if retention is specified, then
LOB$.FLAGS
, which
is obviously a bit vector, is incremented by 32. So the bit that represents 2
5
is set if
RETENTION
is
used. Leveraging our finding, we can write the following query, which uses the function
BITAND
to detect whether
RETENTION
is enabled:
SQL> SELECT owner, object_name,
CASE WHEN bitand(l.flags, 32)=0 THEN l.pctversion$
ELSE NULL
END AS pctversion,
CASE WHEN bitand(l.flags, 32)=32 THEN l.retention
ELSE NULL
END AS retention
FROM sys.lob$ l, dba_objects o
WHERE l.lobj#=o.object_id
AND o.object_type='LOB'
AND OWNER='NDEBES';
OWNER OBJECT_NAME PCTVERSION RETENTION
------------------------------ -------------------- ---------- ----------
NDEBES BLOG_IMG_BLOB 10
NDEBES BLOG_TEXT_CLOB 10800
The result of this query is in line with the
CREATE
TABLE
statement executed earlier. Direct
access to the dictionary base table
SYS.LOB$
resolved the issue.
