Best method of loading data from one database to another

I have two Oracle databases. They have exactly the same table structure. There are about 15 tables that have various FK relationships.

I need to create a process that can accept an ID (The PK of the parent table for the whole database) and transfer all data in the database related to that key to the second database.

I thought about using Database links. However, it looks like this option is not "sanctioned".

What other good options are there to do this? I know there is the import/export tool for MSSql Server but I'm on Oracle. I could create a client application that would handle connecting to both databases but that seems like a lot of work for the c开发者_JS百科lient app. I could export to files and then import but once again that's a lot of work.

Any ideas of a good way to do this or am I pretty much stick with the solutions I already thought of?

I know this might be a tough questions to really have a solid answer, I thank everyone in advance for their help!

There are two complementary command line tools called exp and imp for oracle. You can find some more info on them here http://www.orafaq.com/wiki/Import_Export_FAQ

The main bit you might be interested in is the where clause option

exp scott/tiger tables=emp query="where deptno=10"

@kralco626 - I know this question is old, yet what you could do is create a temp table in the source database that matches the parameters of the original table. Then copy the records you want to move to new database from the original table to the new temp table. syntax:

CREATE TABLE source_new_table
AS (SELECT * FROM source_old_table
    WHERE old_table.column_name = criteria);

Now export the new temp table with datapump export table command. syntax:

EXPDP source_schema_name/source_schema_password TABLES=source_new_table DIRECTORY=datapump_dir DUMPFILE=dumpfile_name.dpdmp LOGFILE=expdp_logfile_name COMPRESSION=ALL

Take that datapump export file and use it to do an datapump import table command to the destination database. syntax:

IMPDP destination_schema_name/destination_schema_password TABLES=source_new_table DIRECTORY=datapump_dir DUMPFILE=dumpfile_name.dpdmp LOGFILE=impdp_logfile_name

Then append the records from that newly imported temp table to the original table in the destination database. syntax:

INSERT INTO destination_old_table
SELECT * FROM source_new_table;

Do this same step for each table that you need to grab data from in the source database. After you're all done then you just need to delete the temp tables from both databases. syntax:

DROP TABLE destination_schema_name.source_new_table;
DROP TABLE source_schema_name.source_new_table;

That should get you the results you want.