December 29, 2011

11g, deferred segment creation, exp, EXP-00003

There is a bug in the export utility on version when running against a (and possiby database that has tables without segments. Deferred segment creation was introduced as a smart feature in 11gR2, but this is causing error EXP-00003 when running the old export utility from lower versions. This is reported on MOS for pre-11g clients, but right now I could not find any reports that this is also a problem with the version when this feature was introduced.

This is a simple test to reproduce the error. Create a couple of tables in an 11gR2 database:

create table empty (id number, foo varchar2(2000) );  
create table aaa (id number, foo varchar2(10));  
insert into aaa values (42,'asdfas');  
select table_name,segment_created   
from user_tables ;  
\------------------------------ ---  

Then perform a simple export from a client:

exp oyise/oracle@orcl file=oyise.dmp log=oyise.log  

Excerpt from the output / logfile:

. about to export OYISE's tables via Conventional Path ...  
. . exporting table AAA 1 rows exported  
. . exporting table EMPTY  
EXP-00003: no storage definition found for segment(0, 0)  

(For export to succeed with one table, create it with a name alphabetically before the EMPTY table).

Note, if the X,Y in “no storage definition found for segment(X,Y)” is non-zero, you have another issue that you probably find a solution for on Oracle Support, e.g. when segment owner <> table owner.

Solution: Upgrade client or allocate one extent for the table that fails:

alter table empty allocate extent;  

You may consider set the parameter DEFERRED_SEGMENT_CREATION to FALSE in case more empty tables will be created.

Also this error does not happen on Data Pump export