Here is a picular case of import export failure.

One of the customers sent me oracle 8.0.5 export dmp.
The user in his export dmp was XX and the tablespace was PRODUCTION. They did a FULL export dmp.

I was trying to import it to here on my test machine where I have a PRODUCTION tablespace but it is used for some other purpose.

I created a tablespace and user as follows:

create tablespace TEST datafile 'C:\DATAFILES\TEST_userdata01.dbf' size 300M
default storage(initial 500K next 500K);
CREATE USER TEST identified by TEST
DEFAULT TABLESPACE TEST
temporary tablespace TEMPORARY1;
GRANT DBA TO TEST;

I did mention default tablespace for user as TEST when I created a user.

Then on my test machine which has oracle 8.0.5 I was trying to import this file doing...at cmd prompt

imp80 system/manager file=c:\temp\exp.dmp log=c:\temp\exp.log fromuser=XX touser=TEST ignore=Y

It did insert rows in some tables..here is the part of log...
. . importing table "MWEBATTRIBAMT" 0 rows imported

. . importing table "MWEBAPPROVAL" 402 rows imported

IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table TEST.MWEBATTRIBCOMMENT by 18 in tablespace PRODUCTION
IMP-00028: partial import of previous table rolled back: 298 rows rolled back

It was trying to insert this user in PRODUCTION tablespace even when I had default as TEST, why ?

When I looked at free space in tablesapce TEST it was all free so it inserted 402 rows from the earlier table into PRODUCTION TABLESPACE which user did those rows went into ????

I have had this problem couple of times before. But I still can't figure out why this would happen. Does this have to do with how they made a export dump. they did a full export



Thanks
Sonali