延迟段创建deferred_segment_creation导致EXP-00003

deferred_segment_creation导致EXP-00003
在11g的版本新特性,为了避免浪费资源,当建立非分区的空表的时候会延迟创建segment,也包括该表相关对象LOBS和INDEX.

官方文档解释:DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, 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.

也是由于该新特性导致通过EXP导出的时候出现'EXP-00003: 未找到段 (0,0) 的存储定义'

sql>show parameter deferred_segment_creation;
deferred_segment_creation            boolean     TRUE
解决方法:

alter system set deferred_segment_creation=false scope=both;
alter table allocate extent;然后再重新导出.

 

在11.2.0.1上更加由于该特性导致引发一个BUG 9285196(METALINK DOC ID:9285196.8)

使用inserts as select  语句插入数据到一个非PARTITION表中会非常慢

解决办法:

插入前先分配SEGMENT

alter table allocate extent;

 

METALINK原文:

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 allocate extent;' will materialize segments for the
table and its dependent objects like indexes and lobs.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/86358/viewspace-739701/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/86358/viewspace-739701/