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.
Advertisement
santosh
September 26, 2011 at 5:21 pm
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
ericwijaya
September 26, 2011 at 7:33 pm
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.
Md Ismail
October 12, 2011 at 4:53 pm
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