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

Combining two export files into one

I have two export.dmp files (A and B). I need to marry them together into one. The database structures are exactly the same. Can I import A and selectively import a date range from B to make one database again?

I have two export.dmp files (A and B). I need to marry them together into one. The database structures are exactly the same. Can I import A and selectively import a date range from B to make one database again? What would be the script to do the selective import? I use Oracle 8i. My vendor is dragging their feet, saying I have to back enter some 30 days of data -- balderdash to that.
The EXP utility includes a QUERY clause which is essentially the WHERE clause of a SELECT statement. By using a QUERY clause, you can export only those rows of data that match your query. Unfortunately, there is no method to selectively import data from a dump file. You will have to prune your data manually. Here is how I would tackle this problem:

  1. Create two users in the database, UserA and UserB.
  2. Import dump file A into UserA with the FROMUSER and TOUSER clauses for the IMP utility.
       imp userid=system file=a.dmp fromuser=scott touser=usera
    
  3. Import dump file B into UserB with the FROMUSER and TOUSER clauses for the IMP utility.
       imp userid=system file=b.dmp fromuser=scott touser=userb
    
  4. Use SQL statements to trim the unwanted rows from the UserA and UserB tables.
  5. Insert the rows from UserB's table into UserA's table.
       INSERT INTO USERA.MY_TAB SELECT * FROM USERB.MY_TAB;
    

You should now have a table containing all the rows you want.

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close