We are trying to import data using a query parameter in expdp, but it fails with errors

Ajay AN

Our database version is Oracle  11.2.0.4.0.

Query parameter in the EXPDP statement we are using is,

query=<schema_name>.<old_table_name>:\"where DATE >= '01-JUL-20'\"

Below are the errors,

ORA-31693: Table data object <object_name> failed to load/unload and is being skipped due to error:

ORA-00904: "JUL": invalid identifier

Can somebody help us with this and let us know what's wrong with above statement?

AJ

I find it easier using a PARFILE when dealing with QUERY.

e.g create a parfile and put your query in there. Then reference that parfile in the expdp commmand

query='"WHERE DATE >=''01-JUL-20''"'

cormaco

Use a date literal instead of a string in your where condition:

where date >= date '2020-07-01'

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html

Ajay AN

Thank you AJ, parfile worked.

We are giving table_exists_action=TRUNCATE and importing the data to old table itself which has constraints, I wanted to follow this method i.e. disable the constarints, trigger impdp with TRUNCATE option and then enable the constarints. Will this work ? and what are the other impacts like impact on indexes and constraints.