import failed

[sonaliak]

Back to my original problem...
I did mention in my original post that I used fromuser touser...
during import I have even posted the command that I used to do this. I have also used default tablespace during that user creation.

This case is picular and happens only when the export is done with the tablespace name same as one of the tablespaces on that server where I am trying to import this dmp.
I do not want to import it to a tablespace PRODUCTION and I cannot get rid of that tablespace either.

The dmp is made by a customer who had a this database on tablespace PRODUCTION.

Any help

thanks

[DBAtrix]

whether you use << touser >> or not, whether you import to the same database or a different one, the theory is the same: if you want the object to go to another tablespace other than the source, the [new] owner cannot have any quota in the source tablespace. the [new] owner must have a different default tablespace on which it has quota.

now, i can't remember if a dba importing will override all these good intentions or not... try importing as the [new] user (unless it was a full export). you could test by having the importing user with revoked quotas *and* revoked unlimited ts. my apologies if you have already tried this.

just be sure that the importing user has
1.) 0 quota on the intended tablespace,
2.) revoked unlimited tablespace,
3.) quota on a different tablespace assigned as the default tablespace.

it's possible, but it can be tricky to get all the pieces right. good luck.
d.

[pando]

I haven´t followed the whole thread but how did you create your test user? I mean what roles did you grant to TEST? You must NOT grant resource to this TEST user if you want him to use a tablespace which is NOT the same as the original database, if you did grant resource you have to revoke this role from this user and grant crate procedure, create trigger to this user if you wanna import procedures and triggers, packages as well

revoking unlimited privilege would not work since test gt this privilege through a role, setting quota 0 would not work, all this if you granted resorce to TEST

[jmodic]

Sonali,

I'm pretty sure where your problem is. You said you first tried to import to user TEST and some of the objects were created in tablespace PRODUCTION, then import terminated because that tablespace was full. Then you revoked UNLIMITED TABLESPACE from your user TEST and also revoked any quota on tablespace PRODUCTION from that user. You've even put the PRODUCTION TS offline, yet still tries to import in that tablespace. I also noticed you are using IGNORE=Y.

So you either did not realy revoke qouta and UNLIMITED TABLESPACE from user *TEST*,
or you forgot to drop TEST's tables that were created in PRODUCTION during your first import. Because you are using IGNORE=Y, import still tries to insert rows in the free space of the existing extents in the tablespace PRODUCTION, although the user TEST has no quota in it.

Try dropping all TEST's tables befor import, or even better: drop user TEST, recreate it with default TS being TEST, revoke UNLIMITED TABLESPACE and QUOTA on PRODUCTION from user TEST, then rerun the import with fromuser-touser and IGNORE=N.

HTH,

[jmodic]

Originally posted by pando
revoking unlimited privilege would not work since test gt this privilege through a role, setting quota 0 would not work, all this if you granted resorce to TEST

Not quite so. UNLIMITED TABLESPACE is not included in any predefined roles (CONNECT, RESOURCE, DBA, etc...), by some strange logic it is granted to users *explicitely as a system privilege* when you grant him some of these roles. It is not part of a role so revoking this system privilege will be sufficient, you don't have to revoke roles.

[amar]

Sorry for the double post. Posted in the wrong thread :-)

Sonali, this is not a case of "peculiar" failure. What is happening is:

When you export, the table creation script is also incuded in the export file. Now the sql statement for creating the table (and any other objects as necessary) always has the "TABLESPACE" clause of the "CREATE TABLE" sql statement attached to it. In your case it will be "TABLESPACE PRODUCTIION".

Verify this by running a command similar to the following:

$ strings expdat.dmp | grep "CREATE TABLE"

When you are trying to import, because you already have the PRODUCTION tablespace the objects are being created there. Had the PRODUCTION tablespace not been there you would have got the error at the first object itself. Otherwise also, if TEST user did not have DBA privileges, you would have got the error at the first object. But being a DBA, TEST user is able to create tables in any tablespace and so the objects are being created and when no space is available you are getting the error.

What you can do is to create a sql script containing all the create table statements and replace the tablespace name with TEST in place of PRODUCTION, or eliminate the TABLESPACE clause altogether.

-amar

[sonaliak]

Thanks a lot, all...
It worked !

Here is what I did..
dropped that user.
recreated that user with dba privileges.

revoked unlimited tablespace from that user.

altered that user to give him 0 quota on the other tablespace.( i think you do not need to do this as revoking unlimited tablesapce will give him 0 quota on all tablesapce including the one that I used as default).

Then give that user quota on that default tablespace( I was missing this giving me error).. I think this is required.

thanks all again

Sonali