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

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