Imported tables do go where they should.

[lavieille]

Using DBA Studio, I created a table space called TEST_TBL_SPC and a user called TEST_USR. The new user's default table space is TEST_TBL_SPC. I use imp.exe to import tables into TEST_TBL_SPC by logging in as TEST_USR, but the imported tables go into the SYSTEM table space.

Why isn't this working? I want tables to go into the TEST_TBL_SPC tablespace not SYSTEM.

[sreddy]

in export file, it says the objects should go to system tablespace. You have to import with

fromuser=
touser=
ignore=y

and default 'touser' to TEST_TBL_SPC.

[khammer99]

I have followed the advice of this post, and it still does not work for me!

I need to seperate 2 users that share the same tablespace. I have taken the following steps:

1) Created new tablespace "staging".
2) Created a new user "stage", with connect and resource to the new tablespace "staging" and made it's default tablespace to the new tablespace "staging".
3) Using exp, userid=DBA, I have exported the tables belonging to the old user "old_stage"
4) Using imp, userid=DBA, fromuser='old_stage' touser='stage' ignore=y.

The import still goes to the old tablespace.

What am I missing?

[sreddy]

Try this way.

Create Old tablespace and New tablespace. Grant quota "None" on old tablespace and "Unlimited" on new tablespace and default the tablespace to "New tablespace". It should work.

It was not working cuz, import was creating old tablespace and dumping objects in it. If old tablespace exists and it doesn't have permission to use the tablespace it has to keep the objects in deafult tablespace where it has its quota unlimited.

use the same options as in my prv. posting.

[sln81]

When you grant Resource role to user, then automatically user gets Unlimited Tablespace privilege also. The old table could have been in the system tablespace, so when exported the storage clause might have contained the tablespace as system itself. So you have to see that user does not have unlimited tablespace privilege and has quota only on his default tablespace.

[lavieille]

Thanks. I got it working. I granted my new user connect priveleges only. The tables went into the specified default tablespace without a hitch when I imported them.

[khammer99]

I'm sorry to keep bother everybody about this, but my import is still going to the wrong tablespace...

Update:

1) I have altered the "new_user" to have 0 quota on the old_tablespace (as suggested here).
2) I have granted this user unlimited quota on the new_tablespace.
3) I re-exported the data from the old_tablespace using with the following options:

a) userid=newuser, fromuser=new_user, touser=new_user
b) userid=old_user, fromuser=old_user, touser=new_user
c) userid=new_user, fromuser=new_user, touser=new_user
d) userid=new_user, fromuser=old_user, touser=new_user
e) userid=DBA, fromuser=old_user, touser=new_user
f) userid=DBA, fromuser=new_user, touser=new_user

The reason I tried the export with the "new_user" is a query of dba_tables (and user_tables logged in as the new_user), shows the tablespace the tables or now located (after 2 imports into the wrong tablespace), in as the old tablespace.

Using the "show=y" parameter on the import tells me that all the tables in the export file are going to be created in the old_tablespace, regardless of which options (a-f) above I choose.

How can the import insert rows in tables in the old tablespace when I have set the quota to 0 for the new_user on the old tablespace, and running the import with the new_user as the "userid=", with it's default tablespace defined as the new tablespace ?

[lavieille]

create your user like this:

CREATE USER "NEW" PROFILE "DEFAULT" IDENTIFIED BY "PWDNEW" DEFAULT TABLESPACE "NEW_TBL_SPC" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON NEW_TBL_SPC ACCOUNT UNLOCK;
GRANT "CONNECT" TO "NEW";

import like this:

c:\> imp NEW/PWDNEW file=c:\EXP.dmp fromuser=OLD touser=NEW ignore=y

[khammer99]

Thank-you, Thank-you, Thank-you!!

It worked!!