问题描述: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需保持一致.