Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79023
  • Last Modified:

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
0
asiminator
Asked:
asiminator
1 Solution
 
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
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now