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 —
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’);
exp scott/tiger file=have_fun.dmp tables=have_fun_a, have_fun_b
Export: Release 220.127.116.11.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 18.104.22.168.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.
- 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.
- Application installation time is reduced
- Application which has many objects with future days partitions can save physical space too.