延遲段建立deferred_segment_creation導致EXP-00003

deferred_segment_creation導致EXP-00003
在11g的版本新特性,為了避免浪費資源,當建立非分區的空表的時候會延遲建立segment,也包括該表相關對象LOBS和INDEX.
官方文檔解釋:DEFERRED_SEGMENT_CREATIONspecifies 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.
也是由於該新特性導致通過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 <tname> allocate extent;然後再重新匯出.
 
在11.2.0.1上更加由於該特性導致引發一個BUG 9285196(METALINK DOC ID:9285196.8)
使用inserts as select  語句插入資料到一個非PARTITION表中會非常慢
解決辦法:
插入前先分配SEGMENT
alter table <tname> 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 <tname> allocate extent;' will materialize segments for the
table and its dependent objects like indexes and lobs.