ORA-39166: Object SYS.AUDIT_LOG was not found.ORA-31655: no data or metadata objects selected for jo

问题描述

将保存审计信息的视图dba_audit_trail转存为表audit_log后导出时报错:

[oracle@testrac1 ~]$ expdp \"/ as sysdba\" directory=dmp_dir  dumpfile=audit_log.dmp tables= audit_log

Export: Release 11.2.0.4.0 - Production on Mon Jun 1 09:13:32 2020

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=dmp_dir dumpfile=audit_log.dmp tables=
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39166: Object SYS.AUDIT_LOG was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYS"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Mon Jun 1 09:13:38 2020 elapsed 0 00:00:04

问题原因

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.

解决该报错的方法

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$

• 用exp导出

[oracle@testrac1 ~]$ exp \"/ as sysdba\" file=/home/oracle/audit_log.dmp tables=audit_log

Export: Release 11.2.0.4.0 - Production on Mon Jun 1 10:41:03 2020

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                      AUDIT_LOG   22491788 rows exported
Export terminated successfully without warnings.

参考：
https://blog.csdn.net/cpongo3/article/details/88799201