Database Reference
In-Depth Information
BasicFiles RETENTION
RETENTION
tells the database to retain modified LOB segment data in the LOB segment in accordance with your
database's
UNDO_RETENTION
setting. If you set your
UNDO_RETENTION
to 2 days, Oracle will attempt to not reuse LOB
segment space freed by a modification. That is, if you deleted all of your rows pointing to LOBS, Oracle would attempt
to retain the data in the LOB segment (the deleted data) for two days in order to satisfy your
UNDO_RETENTION
policy,
just as it would attempt to retain the undo information for the structured data (your relational rows and columns) in
the
UNDO
tablespace for two days. It is important you understand this: the freed space in the LOB segment will not be
immediately reused by subsequent
INSERT
s or
UPDATE
s. This is a frequent cause of questions in the form of, “Why is
my LOB segment growing and growing?” A mass purge followed by a reload of information will tend to cause the LOB
segment to just grow, since the retention period has not yet expired.
■
to use
RETENTION
, the BasicFiles LOB must reside in an automatic segment space management (assM)
tablespace. the
RETENTION
parameter is ignored if the BasicFiles LOB resides in a manual segment space management
(MssM) tablespace. see Chapter 10 for a discussion on assM and MssM.
Note
Alternatively, the BasicFiles LOB storage clause could use
PCTVERSION
, which controls the percentage of allocated
(used by LOBs at some point and blocks under the LOBSEGMENT's HWM) LOB space that should be used for
versioning of LOB data. The default of 10 percent is adequate for many uses since many times you only ever
INSERT
and retrieve LOBs (updating of LOBs is typically not done; LOBs tend to be inserted once and retrieved many times).
Therefore, not much space, if any, needs to be set aside for LOB versioning.
However, if you have an application that does modify the LOBs frequently, the default of 10 percent may be too
small if you frequently read LOBs at the same time some other session is modifying them. If you hit an
ORA-22924
error
while processing a LOB, the solution is not to increase the size of your undo tablespace, or increase the undo retention,
or add more rollback segments if you are using manual undo management. Rather you should use the following:
ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n );
and increase the amount of space to be used in that LOBSEGMENT for versioning of data.
SecureFiles RETENTION
SecureFiles use
RETENTION
to control read consistency (just like BasicFiles). In the
CREATE TABLE
output of
DBMS_METADATA
for the SecureFiles LOB, there is no
RETENTION
clause. This is because the default
RETENTION
is set to
AUTO
, which instructs Oracle to retain undo long enough for read-consistent purposes.
If you want to alter the default
RETENTION
behavior, you can adjust it via the following parameters:
MAX
to indicate that the undo should be retained until the LOB segment has reached the
MAXSIZE
specified in the storage clause (therefore,
MAX
must be used in conjunction with the
MAXSIZE
clause in the storage clause).
•
Use
MIN N
if the flashback database is enabled to limit the undo duration for the LOB to
N
seconds.
•
Set
NONE
if undo is not required for consistent reads or flashback operations.
If you don't set the
RETENTION
parameter for SecureFiles, or specify
RETENTION
with no parameters, then it is set to
DEFAULT
(which is equivalent of
AUTO
).
•
Set
