Import

[tansdot]

Hi
My database is 805.
I am trying to import tables from user A to another user B on the same database. Both the users have different default tablespaces. But when I try to import into user B , it puts all the tables in user A's tablespace . I want the tables to be imported in user B's tablespace.

My import script is as follows:

imp dba/dba@db
file=exp.dmp log=log1.l
fromuser=A touser=B ignore=n buffer=128000 commit=y indexes=y rows=y


Please help?

[mike73]

Is tablespace A and B are in the same database ??? If they are Oracle will automatically default the existing tablespace in your database. there is no way for you force Oracle to use tablespace if tablespace a exists

[jmodic]

Sure there is a way.

Suppose your USER_A has default tablespace TS_A, USER_B has default tablespace TS_B. You want to exp/imp tables from USER_A to USER_B, but at the same time you want the tables to be in TS_B, not in TS_A from where they origin.

All you have top do is to revoke UNLIMITED TABLESPACE privilege from user B (if he has been granted this system privilege) and also to revoke him any quota from any tablespace appart from TS_B. If he has sufficient quota in his default tablespace TS_B the tables will be imported into TS_B for him.

[tansdot]

Hi JModic
What is the syntax for :

revoke him any quota from any tablespace appart from TS_B

Thanks

[pipo]

I don't know how to revoke quotas (I don't even know if you can), but whan you can easily do is :

alter user USER_A quota 0M on TS_B;

[mike73]

jmodic,


Can you please give me more detail about "and also to revoke him any quota from any tablespace appart from TS_B." . what about the index tablespace ??? Let say if user a had index A and user b had index b, will it go to index B or how it's going to default ??

What is the command and what do you mean by revoke any quota from any tablespace


thanks


[Edited by mike73 on 09-14-2001 at 02:29 PM]

[uma]

To revoke any quota from any tablespace other than TS_B use
ALTER USER USER_B QUOTA 0 ON
TS_A;

If the user is having the system privilege(unlimited tablespace) use

REVOKE UNLIMITED TABLESPACE from USER_B;
and check if he has enough quota on TS_B.If required increase the quota.Perform the import.
If necessary grant the privilege(unlimited tablespace) again.
regards.



[Edited by uma on 09-14-2001 at 04:09 PM]

[sonofsita]

Hi Uma,
I am sure you can edit the tablespace name in export dump file by replacing the user A's tablespace name with User B's tablespace and then import.
Nothing need to be altered except the tablespace name to which user B defaults.
Try that with one table you shall know that by your self

[mike73]

Sorry, it didn't work.

here what I do:

1. REVOKE UNLIMITED TABLESPACE FROM USER_B
2. ALTER USER USER_NAME QUOTA 0M ON TABLESPACENAME.

the questions is USERNAME= the user A or user B
tablespace_name is tablespace referencing to TS_A or TS_B


if we revoke unlimited tablespace from user B, are you going to get insufficient privileges on tablespace even when we create user B, we use TS_B as tablespace.

I must do something wrong since the index and PK doesn't make it over


Thanks

[Edited by mike73 on 09-14-2001 at 03:55 PM]

[sonofsita]

extracting the ddl from the export dump is possible by using indexfile parameter and then edit that file by replacing the tablespace name with that user b. then execute that file logging in as user b. thus u r precreating the structure and then import row by setting ignore=y parameter.

you may at the max have a problem if the indexes are of directed to a separate tablespace. In that case also you do that by not importing the indexes which you can create by using the extracted ddl from the export dump