0

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.

  • How did you confirm that the table exists and that you have permissions to select from it? – mustaccio Mar 26 at 14:26
  • We are connected to database as SYS user. and when doing a select * from "IAO"."VERIFIC"; we see data; – sabarish jackson Mar 26 at 14:34
0

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)"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.