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
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
Revoke tablespace quota for this test user on production tablespace and then import or
dump the import using indexfile to get the structure , edit that file and run the script and then run import with ignore=y.
But i think first way is easier.
limiting tablespace quota will not affect the user's privs. however, if any segment needs another extent when the quota is restricted, that activity will be stopped. after the import, re-grant the quota(s). just make sure you have ample space during the restricted quota period. it shouldn't be too much of a problem unless you have bad luck.