Thursday, December 29, 2011

EXP-00003

There is a bug in the export utility on version 11.2.0.1 when running against a 11.2.0.2 (and possiby 11.2.0.1) 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 11.2.0.1 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');
commit;
select table_name,segment_created
from user_tables ;

TABLE_NAME SEG
------------------------------ ---
EMPTY NO
AAA YES



Then perform a simple export from a 11.2.0.1 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 11.2.0.1.