Oracle EXP export error EXP-00091 Analysis Solution

Oracle EXP export error EXP-00091 Analysis Solution

Oracle EXP/IMP is an early data backup and restoration tool. It is widely accepted by the industry for its ease-of-use and powerful features. Some development tools (such as PL/SQL Developer) even integrate the embedded EXP/IMP tool. Some domestic development teams also account for a considerable proportion of EXP/IMP in the default data deployment policies.
With the advancement of the Oracle version and the launch of new features, especially the emergence of massive databases in enterprise application environments, the traditional EXP/IMP tool architecture can no longer meet the development needs. Therefore, in the 10 GB version, Oracle released the Data Pump tool running on the server. In version 11, Data Pump has become a mature solution for Data import, export, and backup.

However, in the actual development field, Exp/Imp still has a large market. Although Exp/Imp is not recommended for 11g, many vendors still use the Exp/Imp method to deploy small data applications.

This record is a problem that occurs during deployment and export of Exp. record it and leave it to your friends.

-------------------------------------- Split line --------------------------------------

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------

 
1. Problem Summary
 
The vendor deploys the system online to export and import the final data. Because it is not the final environment and the Data volume of the online system is relatively small, we recommend using the Exp/Imp or Data Pump tool. While exporting system data was successful, an error EXP-00091 was reported. The screenshots are as follows (for security reasons, some information is blocked ):
 

 
 
The export operation is successful. But in the log, although each data table data can be exported, but the corresponding statistical export, the error EXP-00091. Exporting questionable statistics, which indicates the problem of statistic information export.
I have never encountered such a problem before, and there is no relevant information about the operations performed by the vendor before the export operation. The export statements can be obtained as follows:
 

 
There is no additional information process for the export syntax. At that time, I had no idea to deal with it.
 
2. Problem Analysis and search
 
After the error message and statement information is left, the author queries the Oracle MOS official website and hopes to find the corresponding information. In the EXP Utility Reports EXP-91 During Export (Document ID 730106.1), I have found an explanation of this problem.
Oracle statistics are the foundation of CBO. Oracle optimizer has evolved from RBO to CBO in its development process. In the RBO era, optimizer generation rules are solidified in Oracle code in the form of code. While CBO is based on data object statistics. Statistics are a series of metadata information reflected in entities. In the 9i era, CBO and RBO work together, and statistics need to be collected and maintained manually. In addition, due to data changes, in some early CBO versions, "real-time" statistics often fail to obtain the best execution plan.
Therefore, starting with the Exp/Imp tool of 9i, the export of statistics becomes the default behavior of the Exp tool. Exp/Imp is a client tool. The Character Set setting of the client is different between nls_lang and the database server. This problem occurs if the environment variable setting of nls_lang is different from that of the database server.
Next we will try to reproduce the error through experiments to prove our inference.
 
3. Error Simulation
 
We use oracle 11gR2 for testing and simulation. The specific version is 11.2.0.4. The client operating system is Windows.
 
 
SQL> select * from v $ version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production
PL/SQL Release 11.2.0.4.0-Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0-Production
NLSRTL Version 11.2.0.4.0-Production
 
 
Currently, the key parameters of server NLS are as follows. Note that viewing nls information must be at the database level.
 
 
SQL> select * from nls_database_parameters;
 
PARAMETER VALUE
--------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
(Space reasons, omitted ......)
 
 
The formats of Nls_lang environment variables include nls_language, nls_territory, and nls_characterset. The specific format is as follows:
 
NLS_LANG = _.
 

For more details, please continue to read the highlights on the next page:

1 2 Next Page