I am much surprised that Oracle mandated their new features hidden without notification to legacy users. One such example would be the new Deferred Segment Creation feature and Oracle Data Pump (EXPDP/IMPDP) utilities of Oracle 11g.The original EXP/IMP utilities are deprecated and no longer supported in 11g, but its still been shipped with 11g for backward compatibility.Happy news is that the EXPDP is twice faster as compared to EXP, and IMPDP is relatively 15 to 45 times faster as compared to IMP.
Problem:
I have recently exported a 11g schema using 11g EXP utility and tried to import into another 11g Instance using 11g IMP utility. But to my surprise, not all the tables got transferred to the destination instance. Upon troubleshooting the export log, found out that only empty tables i.e. tables with NO ROWS (0 rows) did not exported at all. I concluded this to be a bug at first, but later found out to be a side-effect of a new 'Deferred Segment Creation' feature and not a bug.
Cause:
Deferred Segment Creation - 11g New Feature:
Oracle 11g introduced the new default feature "Deferred Segment Creation". In a nutshell, when you create a new table - no segment is created, no initial extent is allocated, no storage is reserved. And this made the export (EXP) in Oracle 11gR2 to skip the empty tables (with no rows).
Solution:
Easier Way:
Use the new Oracle Data Pump utilities for the export and import:
expdp user/pass@sid DUMPFILE=test.dmp SCHEMAS=TEST
impdp user/pass@sid DUMPFILE=test.dmp
Harder Way:
Alternatively, you can force the allocation of extents on each empty table using the following command in the Source database to export and re-run the export EXP command, which would export the empty tables as well.
ALTER TABLE <table_name> ALLOCATE EXTENT;
NOTE: For New Objects yet to create, We turn off the functionality before creating any objects using the following command.
ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;
