oracle exp statistics,关于exp statistics的问题和简单测试

在数据导出的时候，可能会碰到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