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
Subscribe to:
Posts (Atom)
