How to export empty tables in oracle

Question

I have a big problem When i take a dump of database the empty tables are truncated

How i can export the all tables in oracle 10g

exp SYSTEM/password FILE=expdat.dmp

Answer 1

This might be because of these tables may not be extent allocated. Before taking backup you need to identify all the tables that do not have data. Then alter these tables to allocate extent.

ALTER TABLE <table_name> ALLOCATE EXTENT;

Use the below script to alter all tables they do not have extent allocated.

SELECT 'ALTER TABLE '||table_name||' ALLOCATE EXTENT;' FROM user_tables WHERE segment_created = 'NO';

Copy the output and execute it.

Answer 2

I found another solution here.
Oracle has an option called DEFERRED_SEGMENT_CREATION, default true.

from the docs:

If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

I'll sum up the solution from the above link:

SQL> alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;

Run the output of the following statement:

SQL> select 'alter table ' || table_name || ' move;' from user_tables where num_rows=0;

This made my exp export empty tables as well.

Answer 3

You might consider expdp (data pump) instead. There's a parameter CONTENT=METADATA_ONLY that might get you what you want.