Oracle 21c XE expdp/impdp跨平台问题咨询及正确操作指导

数据情况 #

account_balance表初始有4条数据，删除2条后剩余2条，相关SQL操作及结果如下：

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

操作及问题 #

1. Oracle Linux环境 #

• 成功执行以下expdp命令导出指定行：

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;

• 执行以下impdp命令导入指定行时，若连接数据库则导入暂停，但全表导入无此问题：

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;

2. Windows 10环境 #

• 执行以下expdp命令时出现LRM-00111: no closing quote for value ''000050026'报错，无法完成选择性导出：

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'

问题解答 #

1. Linux环境下导入指定行暂停的原因及解决方法 #

原因分析

• 锁冲突：导入时若有其他会话对account_balance表持有未释放的锁（比如未提交的DML、长时间查询），impdp会等待锁释放，表现为“暂停”。
• 统计信息异常：表的统计信息过期或缺失，导致impdp执行计划异常，引发长时间等待。
• 数据泵进程阻塞：数据泵后台进程（DP0*系列）可能出现异常阻塞，需要排查进程状态。

解决步骤

1. 检查锁状态：执行以下SQL查看表上的锁：

   SELECT * FROM v$lock 
   WHERE type='TM' 
   AND id1=(SELECT object_id FROM dba_objects WHERE object_name='ACCOUNT_BALANCE' AND owner='L3DBA');
   

   若存在锁，找到对应会话并终止：

   ALTER SYSTEM KILL SESSION 'sid,serial#';
   

2. 更新统计信息：

   EXEC DBMS_STATS.GATHER_TABLE_STATS('L3DBA','ACCOUNT_BALANCE');
   

3. 排查数据泵进程：查看导入任务状态：

   SELECT * FROM dba_datapump_jobs WHERE job_name LIKE '%IMPORT%';
   

   若进程状态异常，终止任务后重新执行导入。
4. 简化导入参数：若不需要排除索引和约束，可去掉EXCLUDE=INDEX,CONSTRAINT参数，减少潜在冲突。

2. Windows系统下正确执行expdp和impdp的方法 #

Windows CMD的引号转义规则与Linux不同，原命令的转义方式导致参数解析错误，需调整如下：

正确的expdp命令

使用双引号包裹QUERY参数，内部单引号直接使用（无需转义）：

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

或用两个单引号转义内部单引号：

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

正确的impdp命令

遵循Windows CMD参数规则，直接执行：

impdp L3DBA@DESKTOP_XEPDB1 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

注意：Windows CMD中命令末尾无需加分号，分号是Linux Shell的命令分隔符。

内容的提问来源于stack exchange，提问作者DipayanRay