Oracle 8i Import - How change owners of tables in the exp/imp process


Hi,

I'm trying to import a tablespace from one server to another (both Oracle 8i servers).  The problem is that once the import is performed, the tables in the new tablespace are owned by the original user not the user I used in the touser clause.  ( Original user/tablespace name exists on both servers. ) I dropped the Original User on the second server and that wound up clearing a number of tablespaces.

Server 1 contains:
UserA tablespace owned by UserA

Export cmd:
exp userid=system/<password>@db1 file=backup buffer=102400 owner=(UserA) compress=n

Server 2 contains:
UserA tablespace owned by UserA
UserB tablespace owned by UserB

Import cmd:
imp userid=system/<password>@staging file=d:/backup/PintoBackup.dmp fromuser=(UserA) buffer=102400 touser=(UserB) ignore=y grants=y indexes=y rows=y commit=y

At this point, the tables in the UserB tablespace are owned by UserA, not by UserB as I would expect.  We dropped the UserA tablespace and User and that wound up clearing a number of other tablespaces including UserB.

What I would like to do is to be able to copy data from one tablespace to another but have them completely independent.  (i.e. Be able to drop User A without affecting any other tablespaces.)

How would I do this?  (Preferably using exp/imp)


Thanks!
Daphne Ignatius
dignatiusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schwertnerCommented:
If you would like to put the tables in another tablespace, you have to do following:
1. Revoke the privilege "unlimited tablespace" from user A
2. Give him QUOTA 0 on the old tablespace.
3. create user B and set DEFAULT TABLESPACE the new tablespace
4. give QUOTA UNLIMETED to user B over the new tablespace.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
racherCommented:
I think your mistake is your assumption that tablespaces are owned by users, they are not.
A user has a default tablespace define on creation and that user's objects will use that default tablespace only if the object does not specify a specific tablespace.
When you import the data into UserB it spots that the specific tablespaces exist and thus uses them.
One way round would be to create what you are calling "UserA Tablespace" AFTER the import.
Since this will not exist at the time of the import then it will use UserB's default.

You could always just reorganise the data into the tablepaces you want anyway.

Hope this helps

Graham
0
dignatiusAuthor Commented:

Actually, schwertner's suggestion did not work as is but it put me on the right track.  This is the sequence of events that worked for me:

If you would like to put the tables in another tablespace, you have to do following:
1. Create User B without granting UNLIMITED TABLESPACE and setting DEFAULT TABLEPSPACE to the new tablespace
2. ALTER USER UserB QUOTA UNLIMITED on USERB.
3. ALTER USER UserB QUOTA 0 on USERA.
4. Import the tables using the fromuser and touser clauses.


This forced the tables to be created in UserB tablespace.  

Thanks for all your suggestions!

Daphne Ignatius
0
schwertnerCommented:
It seems that ALTER USER UserB QUOTA UNLIMITED on USERB also makes tablespace USERB default tablespace for that users.
According to Oracle if you do not define DEFAULT TABLESPAce for a user the default tablespace is SYSTEM tablespace. So you take the risk doing so.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.