expdp导sys用户表时报错ORA-39166、ORA-31655

问题描述：expdp导sys用户表时报错ORA-39166、ORA-31655，如下所示：

[oracle@oel ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=test.dmp logfile=expdp.log tables=test

 

Export: Release 11.2.0.4.0 - Production on Tue Nov 8 17:08:00 2022

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=test.dmp logfile=expdp.log tables=test

Estimate in progress using BLOCKS method...

Total estimation using BLOCKS method: 0 KB

ORA-39166: Object SYS.TEST was not found.

ORA-31655: no data or metadata objects selected for job

Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Tue Nov 8 17:08:02 2022 elapsed 0 00:00:01

TEST表信息.

SQL> show user

USER is "SYS"

SQL> select count(*) from test;

 

  COUNT(*)

----------

      2000

异常原因：根据MOS (Doc ID 553402.1)文档，单独导出sys用户表会报此异常，当使用full模式导出，不会报此错误.

以下MOS信息：

SYMPTOMS

DataPump export of table SYS.AUD$ fails with errors:

ORA-39165: Schema SYS was not found.
ORA-39166: Object AUD$ was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 13:49:47

CAUSE

There is a restriction on DataPump export. It cannot export schemas like SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP and WMSYS in any mode. The Utilities Guide indicates the restriction only on full export mode, but the restriction actually applies to all modes.

SOLUTION

采用exp导出方式.

[oracle@oel ~]$ exp \'/ as sysdba\' file=/home/oracle/test.dmp log=/home/oracle/exp.log tables=test

 

Export: Release 11.2.0.4.0 - Production on Tue Nov 8 17:35:05 2022

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

 

About to export specified tables via Conventional Path ...

. . exporting table                           TEST       2000 rows exported

Export terminated successfully without warnings.

说明：成功导出test表，导出前注意数据库字符集与系统环境变量NLS_LANG需保持一致.