Solved

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

Posted on 2003-11-25
4
1,339 Views
Last Modified: 2008-02-01

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
0
Comment
Question by:dignatius
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 80 total points
ID: 9817512
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
 
LVL 2

Assisted Solution

by:racher
racher earned 20 total points
ID: 9817513
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
 

Author Comment

by:dignatius
ID: 9817665

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
 
LVL 48

Expert Comment

by:schwertner
ID: 9817829
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question