Oracle 11gR2 cannot export empty tables with exp solution, 11gr2exp

Oracle 11gR2 cannot export empty tables with exp solution, 11gr2exp

Oracle 11gR2 cannot export empty tables with exp Solution

There is a new feature in 11gR2. 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. If it is set to FALSE, segment is allocated to no empty or non-empty tables. Modify the SQL statement:

SQL> 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.

Iii. Use the following SQL statement to find an empty table

SQL>select \'alter table \'||table_name||\' allocate extent;\' from user_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.

Find an empty table and generate an update TABLE statement:

SQL> select \'alter table \'||table_name||\' allocate extent;\' from user_tables where num_rows=0

Oracle exp cannot export empty tables. How can I export the structure of empty tables? How does imp import an empty table structure?

You can use the ROWS = N parameter during export and import.

Oracle 10g uses the exp command to export a table missing from the DMP file. Who can tell me why?

Are the tables you lack empty data? If it is the method provided by me, it should be able to solve it!
1. Use the following statement to search for an empty table and generate an execution command.
Select 'alter table' | table_name | 'allocate extent; 'from user_tables where num_rows = 0;

2. Export the query result and execute the Export Statement.

3. Then use exp to export the database, and you can completely export the database including empty tables.