I have the following challenge: I have a large Oracle Database # 1 with 100 tables which are linked through one to many and many to many relationship.
I need to copy only certain rows from the main Oracle Database # 1 into a Oracle Database #2 which has the same data structure ( tables) but no data. Is there a tool that helps me automate the load process, taking into account the constraints that arise from one-many and many-many relationships.
It is a relatively simple task to "copy" all the data. How? Using Export/Import Data Pump (or even the original exp/imp utilities).
Although you can apply the WHERE clause to export, I'm not sure that doing that for 100 tables is feasible. You should know how tables are related to each other so that you wouldn't miss a record.
So, if you're about to do it table-by-table, perhaps it is a better option to write (PL/)SQL code which will do the job. A procedure, maybe? Using an IN parameter, you'd be able to copy any set-of-records you want. Of course, you'd have to maintain the procedure if new tables are created or relationships are modifed.
