11g Oracle Solution for exporting table without exporting data to empty table by default

  • 2021-12-11 09:28:39
  • OfStack

11g oracle When exporting a table, the data will not be exported to null by default

1. Oracle11g does not allocate segment to empty tables by default, so when exporting Oracle11g database with exp, empty tables will not be exported.
2. When the parameter deferred_segment_creation is set to FALSE, segment is allocated to both empty and non-empty tables.

In sqlplus, execute the following command:
SQL > alter system set deferred_segment_creation=false; View:
SQL > show parameter deferred_segment_creation;
After setting this value, it will only work on the newly added tables, but will not work on the empty tables established before.


1. Use the following sentence to find 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 contents of the query results and execute the exported statement.

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

Related articles: