Oracle 11gR2 cannot export empty tables with exp

There is a new feature in Oracle 11gR2. When the table has no data, no segment is allocated to save space. Oracle cannot export empty tables when executing export, but there is still a solution.

Solution:

1. insert a row and then roll back to generate a segment.

This method inserts data into an empty table and then deletes it. When segment is generated, the empty table can be exported.

2. Set the deferred_segment_creation Parameter

The default value of this parameter is TRUE. If it is set to FALSE, segment is allocated to both empty and non-empty tables. Modify the SQL statement:

Alter system set deferred_segment_creation = false scope = both;

It should be noted that the value setting does not affect the empty tables previously imported and cannot be exported. It can only be used for the newly added tables. To export an empty table, you can only use the first method.

3. Use the following statement to find an empty table:

Select 'alter table' | table_name | 'allocate extent; 'fromuser_tables where num_rows = 0;

Export the query results and execute the export statement. This statement forcibly modifies the segment value and then exports the empty table.