Solved

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

Posted on 2003-11-25
4
1,315 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
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
schwertner earned 80 total points
Comment Utility
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
Comment Utility
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
Comment Utility

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 47

Expert Comment

by:schwertner
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now