重庆思庄Oracle、Redhat认证学习论坛

标题: expdp导sys用户表时报错ORA-39166、ORA-31655 [打印本页]

作者: denglj 时间: 2022-11-29 19:16
标题: expdp导sys用户表时报错ORA-39166、ORA-31655

问题描述：expdp导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

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

异常原因：单独导sys用户表会报此异常，当使用full模式导出，不会报此错误.

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导出方式.

[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

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.

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

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)