We wanted to export a user, A, and then import him again but into the schema of another user B. A and B don't share the same tablespace. However, it turns out the objects imported have been saved in A's tablespace but owned by B. How could this have happened? We used imp in interactive mode.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Import into non-default user tablespace
- Thread starter haste
- Start date
Two ideas:
Could it be that B's tables already had existed, possibly empty, but in A's tablespace, before the import took place?
Just to be sure that B's default tablespace hasn't been changed,
did you check it with this query:
select username, default_tablespace
from dba_users
where username in ('A','B');
hope this helps
Could it be that B's tables already had existed, possibly empty, but in A's tablespace, before the import took place?
Just to be sure that B's default tablespace hasn't been changed,
did you check it with this query:
select username, default_tablespace
from dba_users
where username in ('A','B');
hope this helps
Hi,
If the B has rights to A's tablespace ( that is his quota is not 0), then the tables will be imported into the tablespace they came from -- To avoid this, revoke any rights to A's tablespace,then when B imports the tables they will be placed in his tablespace instead.
In general, Tables will be placed in the same tablespace where they were exported from ( just the name has to be the same, can be on an entirely different database) as long as the importing user has rights to that tablespace.
![[profile]](/data/assets/smilies/profile.gif "[profile] [profile]")
If the B has rights to A's tablespace ( that is his quota is not 0), then the tables will be imported into the tablespace they came from -- To avoid this, revoke any rights to A's tablespace,then when B imports the tables they will be placed in his tablespace instead.
In general, Tables will be placed in the same tablespace where they were exported from ( just the name has to be the same, can be on an entirely different database) as long as the importing user has rights to that tablespace.
- Thread starter
- #4
To Hoinz:
I have checked the default tablespaces for both A and B and they are different using:
select username, default_tablespace
from dba_users
where username in ('A','B');
To Turkbear:
That was very informative. Revoking the rights may be a problem since both users have the DBA, but I'll see if I can revoke this role with too much ill effects.
Thanks heaps
I have checked the default tablespaces for both A and B and they are different using:
select username, default_tablespace
from dba_users
where username in ('A','B');
To Turkbear:
That was very informative. Revoking the rights may be a problem since both users have the DBA, but I'll see if I can revoke this role with too much ill effects.
Thanks heaps
- Status
Similar threads
- Locked
- Question