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
