关于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 条评论
登录 后参与评论

相关文章

来自专栏数据库新发现

Oracle诊断案例-Job任务停止执行[最终版]

Last Updated: Friday, 2004-11-26 9:48 Eygle

793
来自专栏我的博客

Ubuntu 12.04 LTS 搭建svn,mysql,apache过程

1.apt-get install subversion libapache2-svn libapache2-mod-auth-mysql apache2 my...

2455
来自专栏个人分享

Spark工程开发常用函数与方法(Scala语言)

import org.apache.spark.{SparkContext, SparkConf} import org.apache.spark.sql.{S...

612
来自专栏乐沙弥的世界

使用 Toad 实现 SQL 优化

       It is very easy for us to implement sql tuning by toad.  We need to do is...

462
来自专栏乐沙弥的世界

Oracle 重建索引脚本

      索引是提高数据库查询性能的有力武器。没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难。然而索引在使用的过程中,尤其是在批量的D...

561
来自专栏java系列博客

rownum浅析

1634
来自专栏杨建荣的学习笔记

关于sysdba,sysoper,dba的区别(r3笔记第62天)

关于sysdba,sysoper,dba这些名词在工作中可能接触的比较多,如果接触的环境是服务器端的,sysdba可能是经常用到的。如果是数据库的维护工作,db...

2506
来自专栏杨建荣的学习笔记

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

2687
来自专栏杨建荣的学习笔记

通过shell解析dump生成parfile(r2笔记76天)

当我们得到一个dump文件的时候,总是有些不太确定dump文件中是否含有一些我们原本不希望出现的表,如果在未知的情况下对dump文件进行操作时很危险的,比如我们...

2003
来自专栏杨建荣的学习笔记

10g,11g数据泵的导入问题及解决(19天)

今天在环境上测试expdp/impdp,环境有10.2.0.5.0,11.2.0.2.0的,11g的环境是从10g升级到11gde .是在impdp的时候都报了...

2864

扫描关注云+社区