expdp 导出sys用户下的表报错ORA-39165 和ORA-39166

最新推荐文章于 2024-01-12 18:48:33 发布

coco3600

最新推荐文章于 2024-01-12 18:48:33 发布

阅读量1.6k

点赞数

文章标签：数据库面试

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

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:

#> exp system/<password> file=dumpfile.dmp log=logfile.log tables=sys.aud$

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.

NOTE:
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 Information

About 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宝典）、添加小麦苗微 信，  学习最实用的数据库技术。  
 
  ........................................................................................................................  