Oracle – Using JOINS in QUERY Clause with expdp

oracleoracle-11goracle-11g-r2

I have a task to export data from database using data pump. I have successfully exported database using subquery in QUERY clause. But I want to know can we expdp using joins in QUERY clause?

Best Answer

The QUERY parameter is used to restrict data in the export job. If you want to export number of rows that are exist in another table then you can use subquery in QUERY parameter.

If you want to join two tables and export you can create table by joining two tables. As shown below.

SQL> create table export_tbl as select e.first_name FN, d.department_name DN from employees e join departments d on(e.department_id=d.department_id);

Table created.

Then can export it.

[oracle@testsrv Desktop]$ expdp hr/hr directory=user_dir dumpfile=export.dmp tables=export_tbl logfile=export.log

Alternatively, you can create a view by joining tables and export views(INCLUDE=VIEWS). You have to filter views during import.

Related Question