11g - Missing Tables after successful IMP...

Seriously, If I introduce a new feature, I would not make that feature as default enabled!!! I let the end user make a decision on whether to enable that feature or not.

But, what did 11g do? It enables the new features by default and forcing us to know about them? Well, you know what one way it's good at least we are forced to learn new features immediately instead of waiting for an organization to start using it :)

Ok, enough blogging...

The issue is simple:

Developers starts complaining that there are few tables that are missing in database after the IMPort is done. They are correct. When looked into the database tables, I don't see few tables in there which they exists at the source database when the EXPort was done.

Time to look into IMP Log files (another reason why one has to make sure they provide log= at the time of IMP or EXP). Wondered to see that there is no reference of the missing table in that import log.

So, scan EXPort log nog. Hmm... even the export don't have any reference to those missing tables.

Well, I am pretty sure I did export for all tables. So, what happened? Why is EXP not exporting those few tables???

Looking into the source database where EXP was done, found that the missing tables are actually empty. Meaning there is no records into those tables. So, that does not mean that table is not there? It is there. Why cant it Export?

Oh, there you go...

Its because of 11g new feature called "deferred_segment_creation". This thing is set to TRUE by default and what it does is basically, it wont create a segment in the datafile at the time of table creation.

It only creates a segment at the time when a row gets into that table (need to wipe out the basics that learnt decades ago and get along with the new basics...).

Alright, thats the issue, so, when EXP happens it fetches the objects from dba_segments and this empty table wont have a segment so it wont be exported. end of story...

Well, how can i handle my export then?

Here you have a solution to chose:

1. for already created tables:

a. alter table empty_table allocate extent;

2. for all new tables if you want to create extent immediately then change the value from TRUE to FALSE for the parameter "deferred_segment_creation"

No. I have bunch of empty tables and have to do export import then what? Sorry... your only option is to use datapump (EXPDP and IMPDP).