How to change retention of securefile Lob segment
How to change retention of securefile Lob segment (Doc ID 2175438.1)
we can only specify RETENTION parameter For SECUREFILE LOBs. Also note that you can specify either PCTVERSION or RETENTION for BASICFILE LOBs, but not both.
show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> CREATE test.TABLE cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ;
Table created.
create table test.cust_int (id number,c_lob CLOB) LOB(c_LOB) STORE AS SECUREFILE ;
Table created.
set linesize 300
col owner for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col RETENTION_TYPE for a15
select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';
OWNER TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTI RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- ------- ---------------
TEST CUST_INT C_LOB YES DEFAULT
alter system set undo_retention=1500;
[-PRIMARY-]sys@ORCLD> alter system set undo_retention=1500;
System altered.
[-PRIMARY-]sys@ORCLD> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 1500 <<< UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention
undo_tablespace string UNDOTBS1
[-PRIMARY-]sys@ORCLD>
alter table
modify lob () (retention min ); ie. =1500
alter table cust_int modify lob (c_lob) (retention sec 1500);
alter table test.cust_int modify lob (c_lob) (retention min 1500) ;
Table altered.
OWNER TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- --------------- ---------------
TEST CUST_INT C_LOB YES MIN 1500
alter table test.cust_int modify lob (c_lob) (retention none) ;
set linesize 300
col owner for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col RETENTION_TYPE for a15
select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';
OWNER TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- --------------- ---------------
TEST CUST_INT C_LOB YES NONE
set linesize 300
col owner for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col RETENTION_TYPE for a15
select TABLE_OWNER,COLUMN_NAME,LOB_NAME,DEF_PCTVERSION,DEF_RETENTION,DEF_MINRET from dba_part_lobs where table_name='CUST_INT';
SQL>
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_in_memory_undo boolean TRUE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL> alter system set undo_retention=1200;
System altered.
SQL>
select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';
TABLE_NAME COLUMN_NAM PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
-------------------- ---------- ---------- ---------- --- ------- ---------------
CUST_INT C_LOB YES DEFAULT
SQL>
SQL>
SQL> alter table cust_int modify lob (c_lob) (retention);
Table altered.
SQL> select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
------------------------------ ------------------------------ ---------- -----
CUST_INT C_LOB YES DEFAULT
SQL> alter table cust_int modify lob (c_lob) (retention min 1200);
Table altered.
SQL> select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';
TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
------------------------------ ------------------------------ ---------- ----------
CUST_INT C_LOB YES MIN 1200
To change retention of securefiles use (retention min ) clause.
eg:
alter table modify lob () (retention min );
alter table test.cust_int modify lob (c_lob) (retention none) ;
Table altered.
set linesize 300
col owner for a20
col TABLE_NAME for a20
col COLUMN_NAME for a20
col RETENTION_TYPE for a15
select owner,TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';
OWNER TABLE_NAME COLUMN_NAME PCTVERSION RETENTION SEC RETENTION_TYPE RETENTION_VALUE
-------------------- -------------------- -------------------- ---------- ---------- --- --------------- ---------------
TEST CUST_INT C_LOB YES NONE
alter table test.cust_int modify lob (c_lob) (retention );
Table altered.
alter table test.cust_int modify lob (c_lob) (retention default);
To check
For Non-partitioned LOB
select TABLE_NAME,COLUMN_NAME,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='';
For partitioned LOB
select TABLE_OWNER,COLUMN_NAME,LOB_NAME,DEF_PCTVERSION,DEF_RETENTION,DEF_MINRET from dba_part_lobs where table_name='';
select TABLE_NAME,COLUMN_NAME,PCTVERSION,RETENTION,SECUREFILE,RETENTION_TYPE,RETENTION_VALUE from dba_lobs where table_name='CUST_INT';
