CODEWITHSUNDEEP
oracle-databaseoracle12cexpdp
Vishal5364
Vishal5364

Reputation: 293

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.

Upvotes: 0

Views: 5310

Answers (3)

Arumugaraj Kuthalingam
Arumugaraj Kuthalingam

Reputation: 107

This is worked for me in Solaris (Date value used in query parameter)

expdp xxxxx/xxxx dumpfile=test_table.dmp logfile=test_table.log tables=scott.TEST directory=DUMDIR query=scott.TEST:\"where IMG_SAVE_DTTIM \<\ \'01-\JAN-\19\'\"

For more infor Refer.Doc ID 277010.1

Upvotes: 0

user1979139
user1979139

Reputation: 844

Use a par file file, much easier

with

... QUERY="where bank_id=43"

Cheers Brian

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

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

Upvotes: 0

Related Questions