Solved

oracle imp command line utility

Posted on 2004-04-21
12
79,006 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 42
sum of columns in a row in oracle 3 44
How to find the cost of a stored procedure in Oracle and optimize it ?? 2 36
SQL Syntax Question 9 27
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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
This video shows how to recover a database from a user managed backup

726 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