Turn a oracle11g analysis and solution to the problem of losing the empty table using EXP export

Cannot export empty table solution with exp

The earliest use of Oracle 11g exported data to find that some of the tables are missing, it feels strange, and then finally found the reason. After you find the problem, look at the solution.
11GR2There is a new feature that does not allocate segment when the table has no data when exporting, empty tables are not exported, which results in the loss of some tables during migration. The stored procedure also fails. This thought Exp can have the corresponding control switch, can switch whether to export empty table, looked under help, not much change. Some strange, is not 11 do NOT update exp function, also see some posts said 11gr1g r2r2 exp is all right, heart feeling, how version compatibility so fragile!

Workaround:

First, insertOne line, and then rollback will produce segment .

The method is to insert data into the empty table and then delete it, resulting in segment. When exporting, you can export empty tables.

TwoSetting the deferred_segment_creation parameter

The parameter value is true by default, and segment is assigned when False , whether it is an empty table or a non-empty table. To modify an SQL statement:

Alter system set DEFERRED_SEGMENT_CREATION=FALSE Scope=both;

It is important to note that this value is set to no effect on previously imported empty tables, and still cannot be exported, but can only work on the new tables that are created later. You can only use the first method if you want to export an empty table before.

ThreeFind the empty table and allocate space using the following sentence

Select ' Alter table ' | | table_name| | ' allocate extent; ' from User_tables where num_rows=0;

Export the results of the SQL query, then execute the exported statement, Force the table to allocate space to modify the segment value, and then export the empty table.

Turn a oracle11g analysis and solution to the problem of losing the empty table using EXP export