Solved

oracle imp command line utility

Posted on 2004-04-21
12
78,995 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
12 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
As other posts you need to specify different default tablespace for BILL, say "two" other than "one.
0
 
LVL 13

Expert Comment

by:riazpk
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:GDE
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now