expdp 导出 sys 用户下的表报错 ORA-39165 和 ORA-39166
[oracle@orcltest ~]$ expdp \'/ AS SYSDBA\' directory=DATA_PUMP_DIR tables='SYS.WRH$_ACTIVE_SESSION_HISTORY','SYS.WRM$_SNAPSHOT','SYS.WRH$_EVENT_NAME','SYS.WRH$_SQLCOMMAND_NAME','SYS.WRH$_PLAN_OPERATION_NAME','SYS.WRH$_PLAN_OPTION_NAME','SYS.WRH$_TOPLEVELCALL_NAME' dumpfile=expdp_ash_lhr_01.dmp parfile=/tmp/expdp_ash_lhr_01.par EXCLUDE=STATISTICS VERSION=11.2.0.1 REUSE_DUMPFILES=Y
Export: Release 11.2.0.3.0 - Production on Fri Dec 16 16:49:52 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_03": "/******** AS SYSDBA" directory=DATA_PUMP_DIR tables=SYS.WRH$_ACTIVE_SESSION_HISTORY,SYS.WRM$_SNAPSHOT,SYS.WRH$_EVENT_NAME,SYS.WRH$_SQLCOMMAND_NAME,SYS.WRH$_PLAN_OPERATION_NAME,SYS.WRH$_PLAN_OPTION_NAME,SYS.WRH$_TOPLEVELCALL_NAME dumpfile=expdp_ash_lhr_01.dmp parfile=/tmp/expdp_ash_lhr_01.par EXCLUDE=STATISTICS VERSION=11.2.0.1 REUSE_DUMPFILES=Y
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object SYS.WRH$_ACTIVE_SESSION_HISTORY was not found.
ORA-39166: Object SYS.WRM$_SNAPSHOT was not found.
ORA-39166: Object SYS.WRH$_EVENT_NAME was not found.
ORA-39166: Object SYS.WRH$_SQLCOMMAND_NAME was not found.
ORA-39166: Object SYS.WRH$_PLAN_OPERATION_NAME was not found.
ORA-39166: Object SYS.WRH$_PLAN_OPTION_NAME was not found.
ORA-39166: Object SYS.WRH$_TOPLEVELCALL_NAME was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_03" completed with 8 error(s) at 16:49:53
于是就搜到了如下文章:
ataPump Export (EXPDP) Fails With Error ORA-39165 Schema SYS Was Not Found (Doc ID 553402.1)
该文章给出了如下答案:
1. 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.
2. The Utilities Guide indicates the restriction only on full export mode, but the restriction actually applies to all modes.
MOS : Why Can an Object Not Be Exported? Expdp of SYSTEM User's Table Returns ORA-39166 or ORA-31655 (文档 ID 2114233.1) 列出来了哪些对象不能导出:
Objects (tables, views, schemas, etc) which fall under either of below conditions are not exported with expdp because they are regarded as system maintained objects.
Object is listed in ku_noexp_view.
This view is a union of ku_noexp_tab and noexp$ tables.
Objects that are listed in this view are not exported.
Object is ORACLE_MAINTAINED='Y' in ALL_OBJECTS (and DBA_OBJECTS).
在视图 sys.Ku_Noexp_View 中或 DBA_OBJECTS 的 ORACLE_MAINTAINED 列为 Y 的对象不能导出。
SELECT * FROM sys.Ku_Noexp_View d WHERE d.name LIKE '%WRH%' ;
SELECT * FROM DBA_OBJECTS d WHERE d.ORACLE_MAINTAINED = 'Y' AND D.object_name LIKE 'WR%' ;
解决该报错的方法是:
1. 使用 exp 导出
2.ctas 的方法在不受限制的 schema 下创建表,然后导出该新建的表
3. use the DBMS_AUDIT_MGMT package of Audit Vault to manage and purge audit data (see Note 731908.1). This allows for the facility to move the AUD$ table out of the SYSTEM tablespace, which can negate the need to export the table.
注意: This issue also applies to other SYS owned auditing tables such as FGA_LOG$
In this Document
Symptoms |
Cause |
Solution |
References |
APPLIES TO:
Information in this document applies to any platform.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
Export the table SYS.AUD$ using the traditional export:
Other options are:
- Use CTAS (Create Table As Select) to copy the SYS.AUD$ data into a non-restricted schema.
- Use the DBMS_AUDIT_MGMT package of Audit Vault to manage and purge audit data (see Note 731908.1 ).
This allows for the facility to move the AUD$ table out of the SYSTEM tablespace, which can negate the need to export the table.
This issue also applies to other SYS owned auditing tables such as FGA_LOG$
REFERENCES
BUG:5647223 - New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit InformationAbout Me
........................................................................................................................ ● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除 ● 本文在itpub、博客园、优快云和个人微 信公众号( xiaomaimiaolhr )上有同步更新 ● 本文itpub地址: http://blog.itpub.net/26736162 ● 本文博客园地址: http://www.cnblogs.com/lhrbest ● 本文优快云地址: https://blog.youkuaiyun.com/lihuarongaini ● 本文pdf版、个人简介及小麦苗云盘地址: http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答: http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群号: 230161599 (满) 、618766405 ● 微 信群:可加我微 信,我拉大家进群,非诚勿扰 ● 联系我请加QQ好友 ( 646634621 ) ,注明添加缘由 ● 于 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成 ● 最新修改时间:2019-07-01 06:00 ~ 2019-07-31 24:00 ● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解 ● 版权所有,欢迎分享本文,转载请保留出处 ........................................................................................................................ ● 小麦苗的微店 : https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书 : http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班 : http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麦苗腾讯课堂主页 : https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客户端 扫描下面的二维码来关注小麦苗的微 信公众号( xiaomaimiaolhr )及QQ群(DBA宝典)、添加小麦苗微 信, 学习最实用的数据库技术。 ........................................................................................................................ |
![]() | ![]() |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-2130832/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2130832/