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”

  1. santosh says :

    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 says :

      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.

  2. Md Ismail says :

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: