import failed

[sonaliak]

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

[gpsingh]

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.

[sonaliak]

I do I revoke tablespace for that user ?
Where is the indexfile ?
I am newbie plese help.

thanks again

[gpsingh]

1) Revoke unlimited tablespace privilege and revoke quota
from tablespace production.
Now import


2) imp indexfile=

It shall create the structure of tables and indexes in a file name mentioned.
Edit this file , remove REM and rows....
and run this file
Then import with ignore=y

[aarganesh]

Hi,
Just log in as sys or system or internal and set the quota 0 on production tablespace as what he says and this will work.

Regards
Ganesh .R

[sonaliak]

If I revoke Revoke unlimited tablespace privilege and revoke quota
from tablespace production.

What all things users on PRODUCTION tablespace will not be able to do ?

thanks

[DBAtrix]

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.

d.

[sonaliak]

It still doesn't work.

I did following before import.


Revoke unlimited tablespace from PROD;
THIS PROD USER IS ON PRODUCTION

ALTER USER TEST
QUOTA 0 M ON PRODUCTION;

THEN I did import and I was watching storage manager it still puts stuffs in production tablespace along with test tablespace.
How do I get this to work ?

What all things did it put in production tablespace ?

thanks
sonali

[sonaliak]

Oh, ya, I also tried to make PRODUCTION TABLESPACE OFFLINE FOR THIS IMPORT AFTER REVOKING PRIVILEGES.

When I tried to do import it complained giving error..
Cannot assign objects to tablespace PRODUCTION and my import failed.

So looks like it still tries to put something in production tablespace.

thanks for all you help

Sonali

[DBAtrix]

if you are using << fromuser=XX touser=TEST>>, you don't need to do anything to user PROD. be sure that TEST doesn't have unlimited ts: Revoke unlimited tablespace from TEST;

the only user you have to fiddle with is TEST, as this is the affected user. check that out.

d.