CODEWITHSUNDEEP
oracle-databaseoracle10g
M.SH
M.SH

Reputation: 378

How to export empty tables in oracle

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

Upvotes: 6

Views: 23457

Answers (3)

Jakob
Jakob

Reputation: 808

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.

Upvotes: 3

DCookie
DCookie

Reputation: 43533

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

Upvotes: 3

Dba
Dba

Reputation: 6649

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.

Upvotes: 26

Related Questions