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.

Wordfeud and regular expression in SQL

Three family members invited me to play Wordfeud with them this Christmas. At the end of such a game losing badly to my sister-in-law and with only a few letters left I was thinking about how I could find candidate words using regular expression in SQL.

First I found a list of Norwegian words on the net. I should have taken the effort to get hold of the same word list as Wordfeud is using. The list was loaded into a table using SQL Developer.

Say you want to see if there is any word that start with N and uses only the letters C, F, S, and A. The following SQL may find it:

select word
from norwegians
where regexp_substr(word,'^n[cfsa]+$') is not null ;

For those not familiar with regular expressions, well, just Google or check out any good book on Perl. In short, the ^ and $ means the beginning and end of line (word in this case) respectively, [] is used to create a group of letters; [cfsa] means any letter of c, f, s, and a; + means one or more occurrences.

Now I only need to make a mobile interface to this, and create a function that calculate the score for each word based on Wordfeud's rules. Next Christmas, maybe. I do know there are apps ready to suggest this, but that is not half as fun :-)