Extract certain rows from table and import

Question

I have a requirement to export / extract certain rows (matching some query) of some tables from one oracle database and import those rows in the respective tables of other database.

both source and target tables have same schema.

My question is, .Whats the best way to implement this requirement.?

Heard of oracle export data pump utility but I have to extract only certain rows.Or Is there any other way to achive this programmatically.

thanks in advance.

Answer 1

You can specify your queries per table with the QUERY parameter with Data Pump export. For example:

QUERY=employees:"WHERE department_id > 10 AND salary > 10000"

With the above, only those rows are exported from the employees table, that have department_id > 10 AND salary > 10000.

More information in the official documentation:

http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL860

Another way to transfer the data: create a database link, and just simply run an INSERT ... SELECT statement.

For example, on the target database:

create database link source connect to username identified by password using 'SOURCE_TNS';
insert into table1 select * from table1@source where ...;