If you still use the good old export utility, you probably have noticed that empty tables are not exported by the exp utility.
You can test it by simply creating dummy table (don’t insert any data into it) and then try to export the table.
>sqlplus user/pass
SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 9 15:48:54 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> create table foo(id number);
Table created.
SQL>exit
>exp file=foo.dmp log=goo.dmp tables=foo userid=user/pass
Export: Release 11.2.0.1.0 – Production on Fri Apr 9 15:48:00 2010
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
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path …
EXP-00011: USER.FOO does not exist
Export terminated successfully with warnings.
Well, I am pretty sure that the table exists – even without any data in it.
The reason for this behavior is 11.2 new feature ‘Deferred Segment Creation‘ – the creation of a table sent is deferred until the first row is inserted.
As a result, empty tables are not listed in dba_segments and are not exported by exp utility.
This feature is controlled by the database parameter deferred_segment_creation. It has default value of TRUE. If you set it by FALSE, any newly created table AFTER the change will be exported – nevertheless if it is empty or not.
>sqlplus system/pass
SQL*Plus: Release 11.1.0.7.0 – Production on Fri Apr 9 15:59:02 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> alter system set deferred_segment_creation=false;
System altered.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
>sqlplus user/pass
SQL*Plus: Release 11.1.0.7.0 – Production on Fri Apr 9 15:59:20 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> create table foo2(id number);
Table created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
>exp file=foo2.dmp log=foo2.log tables=foo2 userid=user/pass
Export: Release 11.1.0.7.0 – Production on Fri Apr 9 15:59:54 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Export done in WE8MSWIN1252 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path …
. . exporting table FOO2 0 rows exported
Export terminated successfully without warnings.
—————————————————————————————————————–
Note: Deferred Segment Creation option is not supported in 11gR2 Standard Edition