CODEWITHSUNDEEP
oracle-databasedata-migrationsql-data-tools
user3049935
user3049935

Reputation: 11

Tool to extract specific data from a Oracle database (with over 100 tables) to another Oracle Database which has the structure but not the data

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.

Upvotes: 0

Views: 199

Answers (1)

Littlefoot
Littlefoot

Reputation: 142798

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.

Upvotes: 2

Related Questions