Solved

oracle imp command line utility

Posted on 2004-04-21
12
79,008 Views
Last Modified: 2011-08-18
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
0
Comment
Question by:asiminator
[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
12 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 125 total points
ID: 10885717
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
 
LVL 8

Expert Comment

by:annamalai77
ID: 10885835
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
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10885854
As other posts you need to specify different default tablespace for BILL, say "two" other than "one.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:riazpk
ID: 10885998
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
 
LVL 48

Expert Comment

by:schwertner
ID: 10886033
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
 
LVL 1

Expert Comment

by:Xavi23
ID: 10886134
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
 
LVL 2

Expert Comment

by:GDE
ID: 10886506
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 10892441
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
 

Author Comment

by:asiminator
ID: 10893495
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
 

Expert Comment

by:Cris_Salgado
ID: 13286200
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13286236
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
 
LVL 48

Expert Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

729 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