Problem solve Get help with specific problems with your technologies, process and projects.

Transferring data in one table between schemas

Using the EXP/IMP utility of Oracle 9i, is it possible to transfer only data of a table in one schema to a table (of same layout) in another schema in some other database server? Sorry if this query sounds too silly... so far I have been deleting the tables that I want to import, then executing the IMP utility.

If the table name is the same, then this is quite easy. The only trick is to tell the IMP utility to ignore the fact that the table already exists. Otherwise, the IMP utility will die with an error. To import, and change the owner, your import parameters will look something like:

imp userid=system file=export.dmp
fromuser=original_owner touser=new_owner
tables=the_table_name ignore=y
The IMP utility cannot change the table name in the process. If that is a requirement, then you'll have to import the data with the existing table name and use SQL statements to move the data to the correct table.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.