[SOLVED] Correct way to take Oracle Data Pump

Correct way to take Oracle Data Pump - expdp, impdp

I am trying to take few rows on oracle linux and windows 10 both of them running Oracle 21c XE Import & Export using expdp & impdp but I faced issues. Both db configurations are identical.

In linux I am able to export 2 rows selectively, in windows I am not able to export any rows selectively. Then I was aiming to import 2 rows back to the table.

The data is as follows:


SQL> select count(*) from account_balance;

  COUNT(*)
----------
         4

SQL> SELECT ROWID, AB.* FROM ACCOUNT_BALANCE AB;

ROWID              KEY_1            CURRENT_BALANCE
------------------ ---------------- ----------------
AAASyQAAMAAAAHrAAA 0000500521000111 12000
AAASyQAAMAAAAHrAAB 0000500521000112 56000
AAASyQAAMAAAAHrAAD 0000500521000114 36700
AAASyQAAMAAAAHsAAA 0000500521000113 679000

SQL> DELETE FROM ACCOUNT_BALANCE WHERE KEY_1 = '0000500521000111';

1 row deleted.

SQL> DELETE FROM ACCOUNT_BALANCE WHERE KEY_1 = '0000500521000113';

1 row deleted.

SQL> select count(*) from account_balance;

  COUNT(*)
----------
         2

SQL> SELECT ROWID, AB.* FROM ACCOUNT_BALANCE AB;

ROWID              KEY_1            CURRENT_BALANCE
------------------ ---------------- ----------------
AAASyQAAMAAAAHrAAB 0000500521000112 56000
AAASyQAAMAAAAHrAAD 0000500521000114 36700

SQL> desc account_balance;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 KEY_1                                     NOT NULL CHAR(16)
 CURRENT_BALANCE                                    CHAR(16)

SQL> SELECT table_name, owner FROM all_tables WHERE table_name='ACCOUNT_BALANCE' ORDER BY owner, table_name;

TABLE_NAME           OWNER
-------------------- --------------------
ACCOUNT_BALANCE      L3DBA

Export Query Used (Oracle-Linux):

expdp L3DBA@VIRTUAL_PDB1 tables=account_balance directory=DATA_PUMP_DIR QUERY=\"WHERE KEY_1=\'0000500521000113\'\" dumpfile=acct_bal_0000500521000113_bkp.dmp logfile=acct_bal_0000500521000113_bkp.log;
expdp L3DBA@VIRTUAL_PDB1 tables=account_balance directory=DATA_PUMP_DIR QUERY=\"WHERE KEY_1=\'0000500521000111\'\" dumpfile=acct_bal_0000500521000111_bkp.dmp logfile=acct_bal_0000500521000111_bkp.log;

Export Query Used (Windows-10):

expdp L3DBA@DESKTOP_XEPDB1 tables=account_balance directory=DATA_PUMP_DIR QUERY=\"WHERE KEY_1=\'0000500261000112\'\" dumpfile=acct_bal_bkp20230504_3.dmp logfile=acct_bal_bkp20230504_3.log;
LRM-00111: no closing quote for value ''000050026'

Import Query Used (Oracle-Linux):

impdp L3DBA@VIRTUAL_PDB1 tables=account_balance directory=DATA_PUMP_DIR dumpfile=acct_bal_0000500521000113_bkp.dmp logfile=acct_bal_0000500521000113_imp.log CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND EXCLUDE=INDEX,CONSTRAINT;
impdp L3DBA@VIRTUAL_PDB1 tables=account_balance directory=DATA_PUMP_DIR dumpfile=acct_bal_0000500521000111_bkp.dmp logfile=acct_bal_0000500521000111_imp.log CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND EXCLUDE=INDEX,CONSTRAINT;

What I noticed is that while I was connected to the DB while importing select rows importing was paused. Same was not in the case when I tried to import Full Table (same table). What might be the cause of this?

Additionally how do I correctly perform expdp and impdp on windows system for same data?

Solution

Windows in particular is quite picky about quotes and the like on the command line. For me, I just throw things in a PARFILE to make life easier

par.dat
=======
tables=account_balance 
directory=DATA_PUMP_DIR 
QUERY="WHERE KEY_1='0000500261000112'" 
dumpfile=acct_bal_bkp20230504_3.dmp 
logfile=acct_bal_bkp20230504_3.log;

expdp user/pass parfile=par.dat