Change retention for Securefile and Basicfile LOB Segments

We can’t simple change the LOB segments retention as in Basicfile or SECUREFILE.
Both have the different methods to change retention.
BASICFILE retention depends upon the undo_retention period set for the whole database.
SECUREFILE retention act as in-depended for LOB segments .

We tried BASICFILE as SECUREFILE and getting the below error:

--Tried on BASICFILE getting following error: (used only on SECUREFILE table)
SQL> alter table cust_int modify lob (c_lob) (retention min 900);
alter table cust_int modify lob (c_lob) (retention min 900)
*
ERROR at line 1:
ORA-43856: Unsupported LOB type for SECUREFILE LOB operation


Change retention of LOB Segment on BASICFILE

1. Check undo retention.

Show parameter undo_retention

NAME            TYPE     VALUE
--------------- -------- -----------
undo_retention  integer  1800

2. Create table with LOB object in BASICFILE.

SQL> CREATE TABLE TEST_INT (id number,c_lob CLOB) LOB(c_LOB) store as basicfile;
Table created.


3. Check the retention for BASICFILE lob segments

col table_name for a10
col column_name for a11
select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='TEST_INT';

TABLE_NAME COLUMN_NAME PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE
---------- ----------- ---------- ---------- --- ------- ---------------
TEST_INT   C_LOB                  1800        NO YES

4. Change undo_retention parameter and then change LOB segment retention:

-- Change system undo retention
Alter system set undo_retention = 900;
--Change table LOG retention as system retention
alter table TEST_INT modify lob (c_lob) (retention);


5. Verify the BASICFILE undo retention changes.

SQL> select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='TEST_INT';

TABLE_NAME COLUMN_NAME PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE
---------- ----------- ---------- ---------- --- ------- ---------------
TEST_INT   C_LOB                         900 NO  YES

Change retention of LOB Segment on SECUREFILE

1. Check the undo retention.

Show parameter undo_retention

NAME            TYPE     VALUE
--------------- -------- -----------
undo_retention  integer  1800

2. Create table with LOB object in SECUREFILE.

SQL> CREATE TABLE TEST_INT (id number,c_lob CLOB) LOB(c_LOB) store as SECUREFILE;
Table created.


3. Check the retention for base file lob segments.

col table_name for a10
col column_name for a11
select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='TEST_INT';

TABLE_NAME COLUMN_NAME PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE
---------- ----------- ---------- ---------- --- ------- ---------------
TEST_INT   C_LOB                             YES DEFAULT

4. Change retention of LOB Segments for securefile:
Note: You need to sepecify retention type for securefile as MIN, MAX, AUTO, NONE
RETENTION [ { MAX | MIN integer | AUTO | NONE }
MAX: keeping old versions of LOB blocks until the space reached the MAX size.
MIN(seconds): use a retention time of the specified seconds.
AUTO: Oracle automatically maintained the retention as space or time need.
NONE: No retention period and space can be reused as needed.

alter table test_int modify lob (c_lob) (retention min 1200);


5. Verify after change the retention

select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='TEST_INT';

TABLE_NAME COLUMN_NAME PCTVERSION  RETENTION SEC RETENTI RETENTION_VALUE
---------- ----------- ---------- ---------- --- ------- ---------------
TEST_INT   C_LOB                             YES MIN                1200