Thursday, December 29, 2011


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

1 comment :

Anonymous said...

I am facing this issue in