oracle-databaseoracle12cexpdp

How to use query clause in EXPDP


I am exporting data of a table through EXPDP by using the below command

nohup expdp \"/ as sysdba\" directory=DATA_PUMP_DIR dumpfile=EXP.dmp logfile=EXP.log version=11.2.0.4 TABLES=CRM.CATEGORIES QUERY='"where bank_id='43'"'

Getting the below error

ORA-31693: Table data object "CRM"."CATEGORIES" failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-01722: invalid number

If I remove the query parameter from the export command, it works fine. I have tried changing the version parameter to different values but still getting the same error The BANK_ID column in the table is of NVARCHAR2 data type. I can't use EXP as it is blocked on the db. Oracle version is 12.1.0.2.0 for both source and destination db.


Solution

  • can you try to alter your query filter in this way:

    QUERY=(CRM.CATEGORIES:"WHERE bank_id='43'")
    

    please be aware of the :

    Edit I Managed to make it work in this way:

    expdp \"/ as sysdba\" dumpfile=DATA_PUMP_DIR:test.dmp tableS=CRM.CATEGORIES QUERY='(CRM.CATEGORIES:"WHERE bank_id=43")'   logfile=DATA_PUMP_DIR:test.log