expdp導(dǎo)sys用戶表時報錯ORA

問題描述：expdp導(dǎo)sys用戶表時報錯ORA-39166、ORA-31655，如下所示：

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

Export:Release 11.2.0.4.0 - Production on Tue Nov 29 14:08:00 2022

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

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

With thePartitioning, 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

Estimatein progress using BLOCKS method...

Totalestimation 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) atTue Nov 29 14:08:02 2022 elapsed 0 00:00:01

TEST表信息.

SQL>show user

USER is"SYS"

SQL>select count(*) from test;

COUNT(*)

----------

2000

異常原因：單獨導(dǎo)sys用戶表會報此異常，當(dāng)使用full模式導(dǎo)出，不會報此錯誤.

There isa 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 therestriction only on full export mode, but the restriction actually applies toall modes.

解決方案：采用exp導(dǎo)出方式.

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

Export:Release 11.2.0.4.0 - Production on Tue Nov 29 14:35:05 2022

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

Connectedto: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bitProduction

With thePartitioning, OLAP, Data Mining and Real Application Testing options

Exportdone in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About toexport specified tables via Conventional Path ...

. .exporting table TEST 2000 rows exported

Exportterminated successfully without warnings.

說明：成功導(dǎo)出test表，導(dǎo)出前注意數(shù)據(jù)庫字符集與系統(tǒng)環(huán)境變量NLS_LANG需保持一致