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:
- Create two users in the database, UserA and UserB.
- 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
- 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
- Use SQL statements to trim the unwanted rows from the UserA and UserB tables.
- 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