Delay segment creation deferred_segment_creation causes EXP-00003

Source: Internet
Author: User
Tags metalink

Deferred_segment_creation causes EXP-00003
New features in version 11G. To avoid wasting resources, creating a non-partitioned empty table will delay the creation of segment, including the table-related objects LOBS and indexes.
Explanation of official documentation: 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.
This new feature also causes 'exp-00003: The storage definition of the segment () not found during EXP export'
SQL> show parameter deferred_segment_creation;
Deferred_segment_creation boolean TRUE
Solution:
Alter system set deferred_segment_creation = false scope = both;
Alter table <tname> allocate extent; then export again.
 
This feature causes a BUG 9285196 (metalink doc id: 9285196.8) in 11.2.0.1)
Inserting data into a non-PARTITION table using the inserts as select statement will be very slow.
Solution:
Assign SEGMENT before inserting
Alter table <tname> allocate extent;
 
METALINK Original:
Slower inserts as select into a non-partitioned table,
Especially if the select involves reading into 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
Table and its dependent objects like indexes and lobs.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.