刚为用户升级到11.2.0.4.160419,但居然还会出现此类大多数在9.2下出现的问题:
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 TAMIC 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 TAMIC 的对象类型定义
即将导出 TAMIC 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 TAMIC 的表通过常规路径...
. . 正在导出表 AA1导出了 34 行
. . 正在导出表 AAA导出了 24602 行
. . 正在导出表 AAAA导出了 46 行
. . 正在导出表 AABORMAS
EXP-00003: 未找到段 (1024,0) 的存储定义
. . 正在导出表 AACBKMAS
熟悉看过11.2的online doc的Concept的同学应该知道:
DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set totrue, then segments for
non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
很多dba的判断一般是指由表结构却没有实际分配segment存储所致,确实如此,在9.2之前的版本非常常见,但在11.2后Oracle新增量一个
deferred_segment_createon的参数,默认为True,来延迟索引段和lob段的创建。
也是由于该新特性导致通过EXP导出的时候出现EXP-00003的错误,查询该数据库的deferred_segment_createon参数:
sql>show parameter deferred_segment_creation;
--------------------------- ------------- ---------
deferred_segment_creation boolean TRUE
--------------------------- ------------- ---------
deferred_segment_creation boolean TRUE
参考MOS给出的Workaround方法:
Slower inserts as select into a non-partitioned table ,
especially if the select involves reading many rows or an expensive join.
This fix changes the behavior. such that the segments will be allocated
regardless if the INSERT inserts rows or not.
Workaround
Materialize segments for the table before issuing the insert as select.
'alter table <tname> allocate extent;' will materialize segments for the
table and its dependent objects like indexes and lobs.
especially if the select involves reading many rows or an expensive join.
This fix changes the behavior. such that the segments will be allocated
regardless if the INSERT inserts rows or not.
Workaround
Materialize segments for the table before issuing the insert as select.
'alter table <tname> allocate extent;' will materialize segments for the
table and its dependent objects like indexes and lobs.
最终采用的方法,禁用该参数,然后与分配一个存储给问题表:
alter system set deferred_segment_creation=false scope=both;
alter table <tname> allocate extent;
alter table <tname> allocate extent;
其他参考的Note:
Error During Schema Level Export - EXP-00003: no storage definition found for segment(0, 0)(文档 ID 1969081.1)