Oracle 11gR2 – EXP-00011 occurs even object exist in schema

Posted: January 20, 2011 in tools
Tags:

Have you ever encounter an issue when oracle 11gR2 exp ( traditional tool ) do not export object which is exist in schema?  If yes, you may get hint here why?

First of all – why one would/should use exp/imp tools where in new oracle datapump is available.  Yeah, Yeah!! But, in many organizations by that time every database(s) come on same version one has to support all sorts of applications which is serving as bridge in heterogeneous network.

Imagine that – 11gR2 DB is a source for one of 8i DB.  And, we have to supply data using export only. You have no choice other than using our classic traditional tool exp.

Lets do practical discussion –

connect scott/tiger
create table hv_fun_a (no number, value varchar2(20));

create table hv_fun_b (no number, value varchar2(20));
insert into have_fun_b values (1, ‘rakesh’);
commit;

exp scott/tiger file=have_fun.dmp tables=have_fun_a, have_fun_b

Export: Release 11.2.0.1.0 – Production on Wed Jan 19 10:38:10 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
About to export specified tables via Conventional Path …

EXP-00011: SCOTT.HAVE_FUN_A does not exist

. . exporting table                  HAVE_FUN_B             1 rows exported
Export terminated successfully with warnings.

Darn! What happened?  I had created have_fun_a so oracle should have exported it.  I bet, it is an oracle  bug.. we like to throw as many as blames to our best friend oracle server.

Oracle has introduced new feature called deferred segment creation.

Beginning in Oracle Database 11g Release 2, when creating a non-partitioned heap-organized table in a locally managed tablespace, table segment creation is deferred until the first row is inserted.  This is set via setting the initialization parameter DEFERRED_SEGMENT_CREATION TRUE, which is default when you create DB.  Having said that unless you insert first record object is not visible to our traditional exp tool.

How do I resolve this behavior?

Option – 1: Add dummy entry into have_fun_a and let oracle to allocate first extent one-time.

Option – 2: Create a table with segment creation immediate option.

create table have_fun_a (no number, value varchar2(20)) segment creation immediate;

Option 3: set initialize parameter DEFERRED_SEGMENT_CREATION=FALSE.  It requires bounce.

Option 4: upgrade target db to 11g and use datapump

How to find objects which are invisible to exp tool?

There is a new SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS that can be used to verify deferred segment creation.

select owner, table_name, segment_created from dba_tables; – if it is set to NO means exp will throw IMP-00011 error.

Advantages?

  1. A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
  2. Application installation time is reduced
  3. Application which has many objects with future days partitions can save physical space too.


About these ads
Comments
  1. gaurang says:

    First of all thanks a lot for such informative article.
    I followed option-1. I did enter one record into the table and tried to run the command again but it didn’t work.
    E.g. INSERT INTO table_name VALUES (value1, value2, value3,…)

    Did I miss anything?

  2. gaurang says:

    Let me add that, that table already contains number of records into it.

  3. Alok says:

    Hello thanks for this informative article. I have a similar issue here for one of the tables in Peoplesoft . This table alreday contains lot of records but doesnt have any row in segment table. Do you think i should recreate this table and insert rows into it. Will DB create segment then?

    Anticipating your response.

    Thanks,
    APH

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