Oracle lob retention修改方法

lob的retetion有独立的设置，在修改undo_rentention后，如果没有修改lob retention，还是会遇到ORA-01555等报错。根据 （1507116.1）方式修改：

select max(maxquerylen) from v$undostat;
SQL>ALTER SYSTEM SET UNDO_RETENTION = 16500 scope=both sid='*';
SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (pctversion 5);
SQL> alter table <SCHEMA_NAME>.<TABLE_NAME> modify lob(<LOB_COLUMN_NAME>) (retention);
select retention from dba_lobs where owner='<SCHEMA_NAME>' and table_name='<TABLE_NAME>';
##注意  
1、versions =< 10.2.0.5的版本，可能需要重启才会生效。
2、文档中没有提示，但必须ASSM管理方式才支持修改retention。MSSM只能使用pctversion方式管理lob undo。
3、pctverseion 与 lob retention是互斥的，只能选择一个。pctversion是预留lob所在表空间的百分比保留UNDO。比如pctversion 5,表空间100g,那么会预留5G（100*0.05）数据保留UNDO。这部分预留空间在没有使用完前不会被覆盖。
复制

普通表按照上述方式是没有问题（测试版本10.2.0.4、11.2.0.4）

查看T11表lob retention是16500，现在按照上述方案改为900
SQL> select pctversion,retention from dba_lobs where owner='T1' and table_name='T11';

PCTVERSION  RETENTION
---------- ----------
                16500		 
ALTER SYSTEM SET UNDO_RETENTION = 900 scope=both;

alter table t1.t11 modify lob(c2)(pctversion 5);
select pctversion,retention from dba_lobs where owner='T1' and table_name='T11';

PCTVERSION  RETENTION
---------- ----------
         5				
		 
alter table t1.t11 modify lob(c2)(retention);
select pctversion,retention from dba_lobs where owner='T1' and table_name='T11';

PCTVERSION  RETENTION
---------- ----------
                  900
复制

分区表（不支持10G）

建立分区表：
CREATE TABLE t1.list(
     dname VARCHAR2(10),
     DATA blob
)PARTITION BY LIST(dname)
(
      PARTITION part01 VALUES('XXXX'),
      PARTITION part02 VALUES('YYYY')
);

默认使用pctversion：
SQL> select pctversion,retention from dba_lobs where owner='T1' and table_name='LIST_T';

PCTVERSION  RETENTION
---------- ----------
         5

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     16500
SQL> ALTER SYSTEM SET UNDO_RETENTION = 900 scope=both sid='*';

System altered.

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900


SQL> alter table t1.list_t modify lob(data)(retention);

Table altered.

SQL>  select pctversion,retention from dba_lobs where owner='T1' and table_name='LIST_T';

PCTVERSION  RETENTION
---------- ----------
         5
修改之后好像没有生效，retention为null。11g开始新增字段DEF_RETENTION辅助确认如下（仅支持11g）：
SQL> select DEF_RETENTION from dba_part_lobs where table_name='LIST_T';

DEF_RET
-------
YES

再次修改为pctversion，确认DEF_RETENTION 禁用
SQL> alter table t1.list_t modify lob(data)(pctversion 5);

Table altered.

SQL> select DEF_RETENTION from dba_part_lobs where table_name='LIST_T';

DEF_RET
-------
NO
分区表的修改灵活是确实很差。普通表可以在修改为lob retention后，再将系统undo_retention参数改小，避免空间浪费。但在分区表中，无法单独设置lob 的retention,只能与系统undo_retention参数保持，所能控制的只有一个开关。
复制