Using JOINS in QUERY clause in EXPDP

Question

I have a par file that is using JOINS in QUERY clause. But it is not working.

directory=expdp
DUMPFILE=Test_SIT_Query_1table_%U.dmp
encryption_password=oracle
parallel=8
logfile=TEST_SIT_Query_table.log
compression=all
cluster=N
METRICS=Y
query=IAO.VERIFIC:"SELECT /*+Parallel(8)*/ distinct A.* FROM IAO.VERIFIC A JOIN IAO.ELIJ B ON A.RACE_ID = B.RACE_ID WHERE B.CREATE_DT > SYSDATE - 30"
tables=IAO.VERIFIC

Below is the error I am getting.

ORA-31693: Table data object "IAO"."VERIFIC" failed to load/unload and is being skipped due to error:
ORA-00942: table or view does not exist 

Could someone help what could be missing in the above par file.

Answer 1

I could not get the syntax you used working. We typically provide a WHERE clause in QUERY. Example:

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create table t2 as select * from dba_users;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
     23687

SQL> select count(*) from t1 where owner in (select username from t2 where username like 'SYS%');

  COUNT(*)
----------
     15775

Then export:

[oracle@o71 ~]$ expdp bp/bp tables=t1 query=t1:\"WHERE OWNER IN \(SELECT USERNAME FROM T2 WHERE USERNAME LIKE \'SYS%\'\)\"

Export: Release 19.0.0.0.0 - Production on Tue Mar 26 15:26:30 2019
Version 19.2.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "BP"."SYS_EXPORT_TABLE_01":  bp/******** tables=t1 query=t1:"WHERE OWNER IN (SELECT USERNAME FROM T2 WHERE USERNAME LIKE 'SYS%')"
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "BP"."T1"                                   1.839 MB   15775 rows
Master table "BP"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for BP.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/expdat.dmp
Job "BP"."SYS_EXPORT_TABLE_01" successfully completed at Tue Mar 26 15:26:54 2019 elapsed 0 00:00:21

[oracle@o71 ~]$

If IAO.ELIJ really exists, you could use something like this:

query=IAO.VERIFIC:"WHERE RACE_ID IN (SELECT B.RACE_ID FROM IAO.ELIJ B WHERE B.CREATE_DT > SYSDATE - 30)"