oracle11gR2 Solution for reporting EXP 00011 error using exp export command

In the process of training customers, I found a strange phenomenon in the customer database server. The client database server environment is AIX6+Oracle11gR2, as follows:

When executing EXP export, some tables prompt EXP-00011: There is no error in the table, but the query user_all_tables view shows that this table does exist, and the execution of select statement can be successful, but the table is empty, and there is no problem with viewing permissions. Later, by searching the data, it was found that the problem was caused by Deferred Segment Creation parameters.

Deferred Segment Creation, delay segment creation, Oracle11gR2 new parameters, the specific use is when a new object that may have Segment is created, if there is no record in this object that needs to consume an Extent, then Segment will not be automatically created when creating the object, which has the advantage of greatly improving the speed when creating the object. However, since the object does not have an Segment, an EXP-00011 error will be reported when performing an EXP export.

Take the error-reported table cf_template as an example, execute the following query:

SQL> show parameter DEFERRED_SEGMENT_CREATION
 
NAME                                 TYPE                 VALUE
------------------------------------ -------------------- --------------------
deferred_segment_creation            boolean              TRUE

Discover that Deferred Segment Creation is turned on, and then execute:

SQL> select segment_name from user_segments where segment_name='CF_TEMPLATE';
no rows selected

There is no return value, and the database does not create Segment for the CF_TEMPLATE table, which verifies why all errors are reported as empty tables.

The solution is as follows:

1. Set the value of deferred_segment_creation to false

This method is only valid for future tables, and the previous tables have no Segment or no Segment.

2. Declare to create Segment immediately when creating a table

create table XXX (XXX XXX) SEGMENT CREATION IMMEDIATE;

3. For a table that has been created but does not have an Segment, you can execute alter table XXX allocate extent to create an Segment, or insert a piece of data to create an Segment