asked on

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

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

racher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dignatius

ASKER

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

schwertner

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.