oracle imp command line utility

Hello

I am trying to move data between schemas and tablespaces in an oracle 9i database.

Say I have a schema 'bob'.
bob has a table named 'X'.
X lives in the tablespace 'one'.

what I want to do is move the table into a new schema call 'bill'
I have exported the data using exp.
I have imported the adta using imp. (fromuser/touser)
but the problem is that when the table X is imported into bill... it still lives in the old tablespace 'one'.

Is there a way to specify which tablespace to import into???

Thanks

Asim
asiminatorAsked:
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.

catchmeifuwantCommented:
No you cannot specify the Tablespace. Because imp will create the table based on whats in the exp file(which means tablespace is the old one itself).

To avoid this:

1)Create a table with the same name "X" in Bill schema, tablespace "two".

2)import with all options and add "ignore=y"

This will ignore the table creation error and import the data into Bill.X residing in "two"

NOTE: However monitor for any other errors in the log file, as they might be ignored too.
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
annamalai77Commented:
dear friend

u can import the table from one user schema to another user schema provided the user in which u want to import belongs to that schema.

OPTION 1:
step 1:
create user ap identified by ap default tablespace <your tablespace>;

step 2:
imp username/password file=<with full path dump file> fromuser=<from username> touser=<to username> tables=<tablename1, tablename2> log=<logname>

since u have created the new user with default tablespace name as required u, the data will automatically go into that tablespace.

regards
annamalai
0
baonguyen1Commented:
As other posts you need to specify different default tablespace for BILL, say "two" other than "one.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

riazpkCommented:
Its simple:

(1) Revoe unlimited tablespace from bill;
(2) alter user bill quota unlimited on tablespace TSNAME;

Where TSNAME= Name of new Tablespace to which you want to move the data

(3) import the data

and thats it

HTH
0
schwertnerCommented:
This is an old and known problem.

You have to create the new user BILL and assign him quota on the new tablespace.

CREATE TABLESPACE "TSDAUDIT"
LOGGING
DATAFILE  '/usr/home/oracle/db/tsdAudit00.dbf'  SIZE 200M
AUTOEXTEND ON    NEXT 200M   MAXSIZE 16384M
PERMANENT   ONLINE
EXTENT  MANAGEMENT LOCAL
;



CREATE USER bill    IDENTIFIED BY bill
DEFAULT TABLESPACE TSDAUDIT   TEMPORARY TABLESPACE  TSTEMPORARY
QUOTA UNLIMITED ON TSDAUDIT;

ALTER USER bill QUOTA UNLIMITED ON TSIAUDIT;


ALTER USER bill QUOTA 0 ON x;

To make sure that the wrong transfer will not be done temporary assign

ALTER USER bob QUOTA 0 ON x;

This can help.

But if one has created the tables assignig explicitelly TABLESPACE x
the tables will reject to be stored in the new location.
In this case you have to use

ALTER TABLE   .... MOVE ...
to move some tables.

That is!
0
Xavi23Commented:
if you has one or more columns of type LOB in your schema, you must do the first comment from 'catchmeifuwant'
else you must do de second comment from annamalai77
0
GDECommented:
If you want only copy table from one user to another easly run
create table new_shema_name.new_table_name as select * from old_shema_name.old_table_name;
than you can drop old table.

Best regards
Dmitry.
0
slightwv (䄆 Netminder) Commented:
Just another comment among the many.  In 8i and above you can easily move a table to another tablespace:
alter table <tab> move tablespace <new tablespace>;
0
asiminatorAuthor Commented:
Hi

Thanks for all the responses.... Im pretty sure the answer lies here.

I will try your suggestions as soon as possible and award the points, im just a bit snowed under at the moment.

Thanks
0
Cris_SalgadoCommented:
How to modify the tablespace of a index after changing the tablespace of it's table successfully?
May i need to have a partition or not?

The table's command I used was ALTER TABLE table_name MOVE TABLESPACE tablespace_name.
Is there any similar command for alter index???

Thanks!
0
slightwv (䄆 Netminder) Commented:
Cris_Salgado:
Asking questions is a long ago closed thread will probably not get an answer due to the fact that there are only a few of us that will ever see the comment.  I suggest posting a new question.
0
schwertnerCommented:
Create the new tablespace for indexes.
Figure out the definition of the index (use OEM, see DDL option - rifght mouse click). Store it.
Drop the index.
Change the DDL of the index creation adding new tablespace clause.
Create the index.
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.