Tables Skipped During Export From EXPDP In 11g

Posted: March 1, 2013 in Data Pump Utility

Recently i encountered a problem where few tables are skipped
from export backup taken with expdb utility in 11g.Export log
doesn’t give any clue that some tables have been skipped. i only
came to know about missing table after import is done and application
starts giving error.

Well, this is expected behavior in 11g, as tables with no rows
(a row never inserted into table) is skipped from export backup. Till
10g, whenever we create a table, some initals extents are aloocated to
the table which consumes some space without actually having any data.

This is changed in 11g and only when some data is inserted in to table,
only then extents are allocated to table. Suppose, you deleted all the
data from table then also extents remains allocated to table and it got
included in export backup.

This behavior can be changed by setting the parameter DEFERRED_SEGMENT_CREATION
to false, which is “true” by default.

This is a dynamic parameter and can be changed any time.


SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME TYPE VALUE
-------------------------- ------------- --------
deferred_segment_creation boolean TRUE

SQL> alter system set deferred_segment_creation=false scope=both;

System altered.

SQL> show parameter DEFERRED_SEGMENT_CREATION

NAME TYPE VALUE
-------------------------- ------------ -----------
deferred_segment_creation boolean FALSE

If you want to take export backup of empty tables after setting this parameter then you have to allocate initial extents:

alter table

allocate extents;

This is because DEFERRED_SEGMENT_CREATION parameter only works for new tables that will be created not for already existing empty tables.

Advertisements
Comments
  1. nitesh says:

    deferred_segment_creation boolean FALSE

    but still it not get black tables tell as soon as possible i m trouble now

    • orasteps says:

      Use following command to allocate extents to empty tables…

      Alter table table-name allocate extents;

      This will allocate initials extents and your table will be included. Above parameter works for new created tables not for already existing tables.

      You can create a script for such tables and run in once. Sorry for comment that this parameter will correct issue for existing tables. I will change this line.

    • orasteps says:

      If possible you can also use traditional exp method…

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