How Does Oracle export system tables under sys users?
Environment: Solaris 10 + Oracle 10.2.0.4
- 1. Export instances
- 2. References
- Reference
1. Export instances
ExportHIST_HEAD$
AndWRI$_OPTSTAT_HISTHEAD_HISTORY
Two tables:
An error occurred while trying to export expdp. the Export command is as follows:
% nohup exp system/****** file=/tmp/jy/system_exp_2tables.dmp log=/tmp/jy/exp_system_exp_2tables.log tables=sys.HIST_HEAD\$, sys.WRI\$_OPTSTAT_HISTHEAD_HISTORY &
The traditional exp can be exported normally. The export process is shown as follows:
Export: Release 10.2.0.4.0-Production on Friday October 30 13:49:51 2015 Copyright (c) 1982,200 7, Oracle. all rights reserved. connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing options have exported the ZHS16GBK Character Set and AL16UTF16 NCHAR character set. The specified table will be exported through the regular path... the current user has been changed to SYS .. exporting the table HIST_HEAD $ to export the 169225445 rows .. exporting table WRI $ _ OPTSTAT_HISTHEAD_HISTORY
2. References
Document ID 553402.1
APPLIES TO:Oracle Database - Enterprise Edition - Version 10.2.0.3 and laterInformation in this document applies to any platform.***Checked for relevance on 29-Jan-2015***SYMPTOMSDataPump export of table SYS.AUD$ fails with errors:ORA-39165: Schema SYS was not found.ORA-39166: Object AUD$ was not found.ORA-31655: no data or metadata objects selected for jobJob "SYS"."SYS_EXPORT_TABLE_01" completed with 3 error(s) at 13:49:47CAUSEThere 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.SOLUTIONExport the table SYS.AUD$ using the traditional export:#> exp system/<password> file=dumpfile.dmp log=logfile.log tables=sys.aud$
Reference
- DataPump Export (EXPDP) Fails With Error ORA-39165: Schema SYS Was Not Found (Document ID 553402.1)
Note: Even if you do not consider performance issues, truncate table HIST_HEAD $ is not allowed. You can only delete records that you think do not need.
SQL> truncate table WRI $ _ OPTSTAT_HISTHEAD_HISTORY;
The table is truncated.
Used time: 00: 00: 25.98
SQL> truncate table HIST_HEAD $;
Truncate table HIST_HEAD $
*
Row 3 has an error:
ORA-00701: unable to change the objects required for hot boot Database
% Oerr ora 701
00701,000 00, "object necessary for warmstarting database cannot be altered"
// * Cause: Attempt to alter or drop a database object (table, cluster, or
// Index) which are needed for warmstarting the database.
// * Action: None.