ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据泵导入分区表统计信息报错(六)

数据泵导入分区表统计信息报错(六)

原创 Linux操作系统 作者:yangtingkun 时间:2009-10-06 23:50:42 0 删除 编辑

今天在进行数据泵导入操作时,发现一个bug

这篇文章描述问题重现的过程。

数据泵导入分区表统计信息报错(一):http://yangtingkun.itpub.net/post/468/456176

数据泵导入分区表统计信息报错(二):http://yangtingkun.itpub.net/post/468/456378

数据泵导入分区表统计信息报错(三):http://yangtingkun.itpub.net/post/468/489067

数据泵导入分区表统计信息报错(四):http://yangtingkun.itpub.net/post/468/489253

数据泵导入分区表统计信息报错(五):http://yangtingkun.itpub.net/post/468/489433

 

 

在前面的文章中已经找到了问题的解决方法,而且在上一篇的文章中已经推测出了导致问题的大致原因。

这里要根据前面推测的原因尝试重现问题,来最终确定导致文件的原因。

10.2版本的数据库中,模仿当时的操作,利用IMP方式导入索引,检查是否能够重现这个问题。

SQL> create table t_stat (id number, name varchar2(30))
  2  partition by range (id)
  3  (partition p1 values less than (10000),
  4  partition p2 values less than (20000),
  5  partition p3 values less than (maxvalue));

Table created.

SQL> insert into t_stat
  2  select rownum, object_name
  3  from dba_objects;

70959 rows created.

SQL> commit;

Commit complete.

SQL> host
[oracle@yans1 ~]$ imp test/test file=t_stat.dmp ignore=y tables=t_stat

Import: Release 10.2.0.3.0 - Production on 星期二 9 8 15:19:10 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
Import terminated successfully without warnings.
[oracle@yans1 ~]$ exit
exit

SQL> select table_name, index_name
  2  from user_indexes
  3  where table_name = 'T_STAT';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
T_STAT                         IND_T_STAT_ID

SQL> exec dbms_stats.gather_table_stats(user, 'T_STAT')

PL/SQL procedure successfully completed.

模仿当时的情况,创建了一个分区表,并利用exp的逻辑备份建立索引。通过imp导入后发现,问题并没有重新,下面指定rows=n的方式再次执行导入:

SQL> drop index ind_t_stat_id;

Index dropped.

SQL> host
[oracle@yans1 ~]$ imp test/test file=t_stat.dmp ignore=y tables=t_stat rows=n

Import: Release 10.2.0.3.0 - Production on 星期二 9 8 15:20:11 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
Import terminated successfully without warnings.
[oracle@yans1 ~]$ exit
exit

SQL> select table_name, index_name
  2  from user_indexes
  3  where table_name = 'T_STAT';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
T_STAT                         IND_T_STAT_ID

SQL> exec dbms_stats.gather_table_stats(user, 'T_STAT')
BEGIN dbms_stats.gather_table_stats(user, 'T_STAT'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1

这次问题重现了,现在可以确定,问题就是由于导入的时候指定了rows=n,而使得表的统计信息被锁定。

下面检查表的分析情况:

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select table_name, last_analyzed        
  2  from user_tables
  3  where table_name = 'T_STAT';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
T_STAT                         2009-09-08 15:20:11

下面分析当前的SCHEMA,检查这个对象是否会被跳过:

SQL> exec dbms_stats.gather_schema_stats(user)

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed
  2  from user_tables
  3  where table_name like 'T_S%';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -------------------
TEST                           2009-09-08 15:48:59
T_STAT                         2009-09-08 15:20:11

SQL> select sysdate from dual;

SYSDATE
-------------------
2009-09-08 15:49:37

当表T_STAT的统计信息被锁定,收集当前SCHEMA的统计信息时,会跳过这个表的统计信息,这与前面碰到的现在完全一致。

至此确定了导致问题的真正原因。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-616050/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10404097