undo管理方法与lob字段的关系 ORA-30014

来源：互联网发布：女神联盟女神进化数据编辑：程序博客网时间：2024/05/23 19:18

在AUM模式下创建的含lob字段的表，在MUM模式下进行维护会报错

SQL> create tablespace undotbs02 datafile '/u01/app/oracle/oradata/orcl/undo01.dbf' size 1G autoextend on extent management local uniform size 16M segment space management manual;

Tablespace created.
SQL> create rollback segment undo01 tablespace undotbs02;
Rollback segment created.

SQL> alter system set rollback_segments='UNDO01' scope=spfile;

System altered.
SQL> alter system set undo_management='MANUAL' scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 818401280 bytes
Fixed Size 2257680 bytes
Variable Size 532679920 bytes
Database Buffers 281018368 bytes
Redo Buffers 2445312 bytes
Database mounted.
Database opened.
SQL> select name from v$rollname;
NAME
------------------------------
SYSTEM
UNDO01
SQL> alter table sales add partition sales_q2 values less than (1999,05);
alter table sales add partition sales_q2 values less than (1999,05)
*
ERROR at line 1:

ORA-30014: operation only supported in Automatic Undo Management mode

报错原因：分区表的lob字段默认使用retention作为保留前镜像数据的参数，而回滚段手动管理不支持这个参数

此时的处理方法：

SQL> alter table sales modify lob (sale_content) (pctversion 10);
Table altered.
SQL> alter table sales add partition sales_q2 values less than (1999,05);
Table altered.

0 0