Oracle 11g R2 cannot export empty tables with exp Solution

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

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, resulting in a segment. An empty table can be exported.

2. Set the deferred_segment_creation Parameter

The default value of this parameter is TRUE. When it is set to FALSE, segment is assigned to both empty tables 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 result, execute the exported statement, forcibly modify the segment value, and then export the result to export the empty table,

Note: before inserting data into the database, you can modify the 11g_R2 parameter to export an empty table.

Search for empty tables:

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

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12