Shulou(Shulou.com)06/01 Report--

Today, it is required to export some table data.

The exp export script provided is as follows:

USERID=x/x@xBUFFER=102400ROWS=Y LOG=T_DAYLOG_CALLBYSERVICE.logFILE=T_DAYLOG_CALLBYSERVICE.dmptables=T_DAYLOG_CALLBYSERVICEquery= "where logdate between to_date ('20150810) and to_date (' 20150828)"

Attempt to execute error report

Exp parfile=T_DAYLOG_CALLBYSERVICE.PARExport: Release 10.2.0.4.0-Production on Tue Sep 1 16:05:15 2015Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path.... . Exporting table T_DAYLOG_CALLBYSERVICE 10197 rows exportedEXP-00091: Exporting questionable statistics.EXP-00091: Exporting questionable statistics.Export terminated successfully with warnings.

If prompted to report an error EXP-00091, check the error

[oracle@SH-SRV-UIDB:/u01/script] $oerr exp 00091

00091, 00000, "Exporting questionable statistics."

/ / * Cause: Export was able export statistics, but the statistics may not be

/ / usuable. The statistics are questionable because one or more of

/ / the following happened during export: a row error occurred, client

/ / character set or NCHARSET does not match with the server, a query

/ / clause was specified on export, only certain partitions or

/ / subpartitions were exported, or a fatal error occurred while

/ / processing a table.

/ / * Action: To export non-questionable statistics, change the client character

/ / set or NCHARSET to match the server, export with no query clause

/ / export complete tables. If desired, import parameters can be

/ / supplied so that only non-questionable statistics will be imported

/ / and all questionable statistics will be recalculated.

According to the above requirements, it may be that the character set does not match, or the query child is specified at the time of export, the partition or sub-partition is exported, or the error occurred while processing the table (limited translation ability, can only be understood in this way).

Check whether the client and server character sets match

SQL > select userenv ('language') from dual;USERENV (' LANGUAGE')-AMERICAN_AMERICA.ZHS16GBKselect * from nls_database_parameters t where t.parameterized samples NLScalls CHARACTERSETRS; select * from v$nls_parameters where parameter='NLS_CHARACTERSET'

Add language settings to environment variables

Export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'echo $NLS_LANG

Re-export still reported 00091 error, try to remove the query also reported an error, modify the script to try to use the expdp tool to export the table.

USERID=x/x@xDIRECTORY=BACKUPDIRCOMPRESSION=NONECONTENT=ALLLOGFILE=T_DAYLOG_CALLBYSERVICE.logDUMPFILE=T_DAYLOG_CALLBYSERVICE.dmpTABLES=T_DAYLOG_CALLBYSERVICEQUERY= "where logdate between to_date ('20150810) and to_date (' 20150828)"

Check that the log is exported correctly, but careful observation shows that the number of lines exported is the same as that of exp export, which is 10197 lines.

Export: Release 10.2.0.4.0-64bit Production on Tuesday, 01 September, 2015 15:24:24Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP Data Mining and Real Application Testing optionsStarting "ICD". "SYS_EXPORT_TABLE_01": parfile=T_DAYLOG_CALLBYSERVICE.PAR Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 168 MBProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . Exported "ICD". "T_DAYLOG_CALLBYSERVICE" 980.4 KB 10197 rowsMaster table "ICD". "SYS_EXPORT_TABLE_01" successfully loaded/unloaded**** * Dump file set for ICD.SYS_EXPORT_TABLE_01 is: / u01/temp/T_DAYLOG_CALLBYSERVICE.dmpJob "ICD". "SYS_EXPORT_TABLE_01" successfully completed at 15:24:28

In other words, the export should be successful, but an error is reported for some reason.

In the document EXP Utility Reports EXP-91 During Export (document ID 730106.1), the author found an explanation for this problem.

Oracle statistics are the basis of CBO work. In the course of development, Oracle optimizer has experienced the evolution process from RBO to CBO. In the era of RBO, optimizer generation rules are solidified in Oracle code in the form of code. CBO's work is based on data object statistics. Statistics are reflected in the entity, which is a series of metadata information. In the 9i era, CBO and RBO work together, and statistics need to be collected and maintained manually. Moreover, due to data changes, in some early versions of CBO, "real-time" statistics often did not get the best execution plan. So, since 9i's Exp/Imp tool, statistics export has become the default behavior of the Exp tool.

In other words, the exp tool uses statistics when exporting, and the occurrence of errors is related to statistics. The problem has not been solved.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.