关于exp statistics的问题和简单测试(82天)

在数据导出的时候,可能会碰到EXP-00091: Exporting questionable statistics.的问题,有时候会让人有点摸不到头脑,不知道该调整什么。 这个时候NLS_LANG就发挥作用了。

[ora11g@rac1 ~]$ exp n1/n1 tables=t file=t.dmp rows=n 
Export: Release 11.2.0.3.0 - Production on Fri May 23 19:26:35 2014 
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, OLAP, Data Mining and Real Application Testing options 
Export done in US7ASCII character set and AL16UTF16 NCHAR character set 
server uses AL32UTF8 character set (possible charset conversion) 
Note: table data (rows) will not be exported 
About to export specified tables via Conventional Path ... 
. . exporting table                              T 
EXP-00091: Exporting questionable statistics. 
Export terminated successfully with warnings. 

可以通过查询字符集来判断是否和客户端的有出入。?

SQL>  select * from v$nls_parameters where parameter='NLS_CHARACTERSET'; 
PARAMETER 
---------------------------------------------------------------- 
VALUE 
---------------------------------------------------------------- 
NLS_CHARACTERSET 
AL32UTF8 
SQL> host
echo的结果不是没设置,就可能是设置的有问题。重新设置,问题就解决了。 
[ora11g@rac1 ~]$ echo $NLS_LANG 
[ora11g@rac1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 
[ora11g@rac1 ~]$ exp n1/n1 tables=t file=t.dmp rows=n 
Export: Release 11.2.0.3.0 - Production on Fri May 23 19:28:42 2014 
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, OLAP, Data Mining and Real Application Testing options 
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set 
Note: table data (rows) will not be exported 
About to export specified tables via Conventional Path ... 
. . exporting table                              T 
Export terminated successfully without warnings.

当然了,对于这个问题,提示导出的statistics信息有问题,来测试一下在有警告的情况下,导入是否有问题。我们换一个用户,看看统计信息是否能够导入。

[ora11g@rac1 ~]$ exp n1/n1 tables=t file=t.dmp rows=n
Export: Release 11.2.0.3.0 - Production on Fri May 23 19:53:24 2014
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, OLAP, Data Mining and Real Application Testing options 
Export done in US7ASCII character set and AL16UTF16 NCHAR character set 
server uses AL32UTF8 character set (possible charset conversion) 
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ... 
. . exporting table                              T 
EXP-00091: Exporting questionable statistics. 
Export terminated successfully with warnings.

SQL> create table testo.t tablespace pool_data as select *from n1.t where rownum<2;
Table created.
[ora11g@rac1 ~]$ imp testo/oracle file=t.dmp tables=t statistics=always
Import: Release 11.2.0.3.0 - Production on Fri May 23 19:54:01 2014
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, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by N1, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set 
import server uses AL32UTF8 character set (possible charset conversion) 
. importing N1's objects into TESTO 
. importing N1's objects into TESTO 
Import terminated successfully without warnings.

可以看到,没有任何的警告,查看统计信息,发现已经导入了。尽管表里没有数据,但是由于统计信息的导入,显示数据条数还是600多万。

********** TABLE GENERAL INFO *****************
TABLE_NAME                     PAR TABLESPACE STATUS   NUM_ROWS     BLOCKS EMPTY_BLOCKS LOG MON ROW_MOVE LAST_ANAL 
------------------------------ --- ---------- ------ ---------- ---------- ------------ --- --- -------- --------- 
T                              NO  POOL_DATA  VALID     6856704      39174            0 NO  YES DISABLED 23-MAY-14

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-05-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jackson0714

好用的SQL TVP~~独家赠送[增-删-改-查]的例子

1354
来自专栏乐沙弥的世界

SQL Tuning Advisor(STA) 到底做了什么?

      SQL Tuing Advisor(STA) 是Automatic Tuning Optimizer(自动优化调整器)的一部分。在前面的文章使用SQ...

684
来自专栏王硕

原 pg查询树的简单解读

34113
来自专栏null的专栏

MATLAB技巧——sort和sortrows函数

1、sort函数 sort函数用于对数据进行排序,通过help sort命令,可以查找到sort函数的具体用法: Y = SORT(X,DIM,MODE) ha...

2775
来自专栏猿湿Xoong

一个bit一个bit的进行 Base64 白话科普,看不懂算你输

904
来自专栏技术博文

Yii数据库操作方法指南

CDbConnection: 一个抽象数据库连接 CDbCommand: SQL statement CDbDataReader: 匹配结果集的一行记录 CDb...

2367
来自专栏Spark学习技巧

Flink DataSet编程指南-demo演示及注意事项

Flink中的DataStream程序是对数据流进行转换的常规程序(例如,过滤,更新状态,定义窗口,聚合)。数据流的最初的源可以从各种来源(例如,消息队列,套接...

1.3K12
来自专栏java学习

数据库介绍以及使用

第1章 数据库介绍 1.1 数据库概述 l 什么是数据库 数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以...

3194
来自专栏C/C++基础

MySQL数据库的设计和命令行模式下建立详细过程

MySQL数据库管理系统(DBMS)中,包含的MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型,大致可以分为三类:数值、日期/...

670
来自专栏数据库

干货!超过500行的Mysql学习笔记

本文为作者初学Mysql时做的笔记,囊括了Mysql相关基本知识,内容较多超过500行笔记,希望对大家有帮助。 ? /* 启动MySQL */ net star...

1896

扫码关注云+社区