SQL> CREATE TABLE cust\_int (id number,c\_lob CLOB) LOB(c\_LOB) STORE AS SECUREFILE ;
Table created.
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**
非分区 LOB
select TABLE\_NAME,COLUMN\_NAME,SECUREFILE,RETENTION\_TYPE,RETENTION\_VALUE from dba\_lobs where table\_name='<table\_name>';
分区 LOB
select TABLE\_OWNER,COLUMN\_NAME,LOB\_NAME,DEF\_PCTVERSION,DEF\_RETENTION,DEF\_MINRET from dba\_part\_lobs where table\_name='<table\_name>';
LOB参数含义：
| RETENTION \[ { MAX | MIN integer | AUTO | NONE } \]
RETENTION AUTO (default)
Use the value Oracle has automatic tuning as RETENTION period. A value of AUTO tells the system to manage the space as efficiently as possible weighing both time and space needs.
RETENTION MAX
The value of MAX specifies keeping old versions of LOB data blocks until the space used by segment has reached the size specified in the MAXSIZE parameter. If MAXSIZE is not specified, MAX behaves like AUTO.
RETENTION MIN (in seconds)
A value of MIN means to use a retention time of the specified seconds.
RETENTION NONE
A value of NONE means that there is no retention period and space can be reused in any way deemed necessary.
参考文档： How to change retention of securefile Lob segment (文档 ID 2175438.1)
