Oracle 11gR2: export utility doesn’t export empty tables
In Oracle 11g release 2, there are new feature to save disk spaces. There is a new database parameter in Oracle Database 11gR2, DEFERRED_SEGMENT_CREATION. Initially this parameter is set to TRUE. This parameter affect the export utility when exporting schema. The problem is that empty tables will not get exported by conventional export utility. For Export using Data Pump there is no problem with empty tables.
To disable this feature, you need to change the parameter to false:
SQL> alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;
Then you need to move the tablespace of the empty tables or recreate the tables, so that Oracle will recognize the new created segment.
5 responses to “Oracle 11gR2: export utility doesn’t export empty tables”
Leave a comment
Free Storage
My Traffic
Recent Comments
 | Dennis on Change breadcrumb separator on… |
 | Mr. Miles on Windows 7: Install the same up… |
 | conorrobotham on “You do not have suffici… |
 | miles on Windows 7: Install the same up… |
 | acoi kumar on transfer pulsa 3 |
 | Paul A on Windows 7: Install the same up… |
Eric Wijaya 
how to export 11g or import tables which r empty
i am not able to eport the empty table plz give me how to export the empty tables
Hi santosh,
You can just run this command:
SQL> alter system set DEFERRED_SEGMENT_CREATION=FALSE scope=both;
SQL> alter table EMPTY_TABLE_NAMES move;
then you can try exporting the tables.
thanks…
How to do it for all empty tables? Just run the result of following select as a script:
select ‘alter table ‘ || table_name || ‘ move;’ from user_tables where num_rows=0;
If you set DEFERRED_SEGMENT_CREATION to FALSE, any newly created tables after the change will be exported. There are also a few ways to fix that issue for existing empty tables. One of them is below:
ALTER TABLE ALLOCATE EXTENT;
Thanks