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=yThe 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.