当前位置: 首页 > Oracle, oracle 11g > 正文

ORA-20005 object statistics are locked (stattype = ALL)

公司收购了石家庄一家公司,要把客户的数据迁移到我们的数据库中,昨晚加班,进行迁移测试演练,石家庄那面的数据库版本是10gR2,我们的数据库版本是11.2.0.4,使用逻辑迁移的方式进行,迁移大致分为以下几阶段:

  1. 由石家庄那面的DBA通过逻辑导出的方式(EXP)将石家庄的数据导出并发送给我们。
  2. 将石家庄导出的数据导入(IMP)到一个中间库,然后开发人员进行数据转换处理。
  3. 将开发人员处理后的有效数据,通过逻辑迁移的方式(EXP/IMP)导入到正式数据库。
  4. 测试人员进行线上测试。
  5. 删除这部分数据。

我在使用IMP将石家庄的数据导入到中间库的时候,数据导入没有问题,在收集统计信息的时候遇到了ORA-20005错误。

IMP-00003: ORACLE error 20005 encountered
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 3784
ORA-06512: at "SYS.DBMS_STATS", line 4617
ORA-06512: at "SYS.DBMS_STATS", line 9180
ORA-06512: at line 1
Import terminated successfully with warnings.

这个错误是因为有表锁定了统计信息的收集,在收集统计信息的时候就会遇到这个错误,通常是用来固定执行计划才会锁定统计信息的收集,常用的有表级别的锁定,也有SCHEMA级别的锁定,因为我只遇到很少的这个错误,很显然这是表级别的锁定,可以通过USER_TAB_STATISTICS来查看哪些表被锁定了统计信息的收集。

SQL> select table_name from user_tab_statistics where stattype_locked is not null; 

TABLE_NAME
------------------------------
TB_ZZ005

可以看到TB_ZZ005表的统计信息被锁定了,这时候收集这张表的统计信息就会报错。

SQL> exec dbms_stats.gather_table_stats('MIG','TB_ZZ005');
BEGIN dbms_stats.gather_table_stats('MIG','TB_ZZ005'); END;

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

但是通过dbms_stats.gather_schema_stats收集SCHEMA级别的统计信息是不会报错的,但是这张表的统计信息不会被收集,下文会有测试,这里先不摘录。

找到了被锁定收集统计信息的表,可以通过DBMS_STATS包来进行解锁。

SQL> exec dbms_stats.unlock_table_stats('MIG','TB_ZZ005');

PL/SQL procedure successfully completed.

这里需要注意一下,即使是使用MIG用户来解锁MIG用户的表,这里也需要指定MIG用户,语法就是DBMS_STATS.UNLOCK_TABLE_STATS(USERNAME,TABLE_NAME),不指定用户就会报错。

解锁后,就可以收集统计信息了。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('MIG','TB_ZZ005');

PL/SQL procedure successfully completed.

这样,这个问题就解决了。下面在11.2.0.4版本的数据库中测试下表级别和SCHEMA级别锁定统计信息的情况。

测试第一步,创建测试表,我的测试用户下面刚好有三张测试表,就不需要创建了,直接拿过来用就可了。

dbdream@IVLDB> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ROWID_TEST                     TABLE
T_RESTREE                      TABLE
T_TEST                         TABLE

本实验只要使用T_TEST表,这个表只有两条有效数据,其他都是重复数据,一共400多万条记录。

sys@IVLDB> select id,name,count(*) from dbdream.t_test group by id,name;

        ID NAME         COUNT(*)
---------- ---------- ----------
         1 A             2097152
         2 B             2097152

在测试之前,先收集一下统计信息,使统计信息更准确。

dbdream@IVLDB> exec dbms_stats.gather_schema_stats('DBDREAM');

PL/SQL procedure successfully completed.

通过统计信息查看下T_TEST表的数据量,可以看到统计信息是准确的。

dbdream@IVLDB> select num_rows from user_tables where table_name='T_TEST';

  NUM_ROWS
----------
   4194304

dbdream@IVLDB> select count(*) from T_TEST;

  COUNT(*)
----------
   4194304

然后锁定T_TEST表的统计信息。

dbdream@IVLDB> exec dbms_stats.lock_table_stats('DBDREAM','T_TEST');

PL/SQL procedure successfully completed.

现在T_TEST表的统计信息是锁定的,接下来删除T_TEST表中ID=1的记录,这样,这张表的数据量将会减少一半。

dbdream@IVLDB> delete from T_TEST where id=1;

2097152 rows deleted.

dbdream@IVLDB> commit;

Commit complete.

dbdream@IVLDB> select count(*) from T_TEST;

  COUNT(*)
----------
   2097152

这时,通过统计信息查询这张表的数据量,结果就会不准。

dbdream@IVLDB> select num_rows from user_tables where table_name='T_TEST';

  NUM_ROWS
----------
   4194304

此时使用dbms_stats.gather_table_stats进行表级别的统计信息收集,就会报错。

dbdream@IVLDB>  exec dbms_stats.gather_table_stats('DBDREAM','T_TEST');
BEGIN dbms_stats.gather_table_stats('DBDREAM','T_TEST'); END;

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

但是进行SCHEMA级别的统计信息收集是不会报错的。

dbdream@IVLDB> exec dbms_stats.gather_schema_stats('DBDREAM');

PL/SQL procedure successfully completed.

虽然SCHEMA级别的统计信息可以成功收集,但是T_TEST这张统计信息锁定的表是不会收集统计信息的。

dbdream@IVLDB> select num_rows from user_tables where table_name='T_TEST';

  NUM_ROWS
----------
   4194304

下面再测试下SCHEMA级别的统计信息锁定,先解锁T_TEST表的统计信息锁定,然后进行SCHEMA级别的统计信息锁定。

dbdream@IVLDB>  exec dbms_stats.unlock_table_stats('DBDREAM','T_TEST');

PL/SQL procedure successfully completed.

dbdream@IVLDB> exec dbms_stats.lock_schema_stats('DBDREAM');

PL/SQL procedure successfully completed.

dbdream@IVLDB>  select table_name from user_tab_statistics where stattype_locked is not null; 

TABLE_NAME
------------------------------
T_RESTREE
ROWID_TEST
T_TEST

这时候使用表级别的统计信息收集,就会遇到错误,但是SCHEMA级别的统计信息收集是不会遇到错误的。

dbdream@IVLDB> exec dbms_stats.gather_table_stats('DBDREAM','T_TEST');
BEGIN dbms_stats.gather_table_stats('DBDREAM','T_TEST'); END;

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

dbdream@IVLDB> exec dbms_stats.gather_schema_stats('DBDREAM');

PL/SQL procedure successfully completed.

虽然SCHEMA级别的统计信息收集可以成功,但是这都是假象,而且通过查询表的最后一次统计信息收集时间就会发现,这张表的统计信息根本就没有收集。

dbdream@IVLDB> select LAST_ANALYZED from user_tables where table_name='T_TEST';

LAST_ANALYZED
-------------------
2017-09-15 16:10:47

通过统计信息查询表的记录数,也可以看出这张表的统计信息没有被收集。

通过统计信息查询表的记录数,也可以看出这张表的统计信息没有被收集。
dbdream@IVLDB> select num_rows from user_tables where table_name='T_TEST';

  NUM_ROWS
----------
   4194304

下面解锁SCHEMA级别的统计信息,对T_TEST表进行统计信息收集,再次查询统计信息就是准确的了。

dbdream@IVLDB> exec dbms_stats.unlock_schema_stats('DBDREAM');

PL/SQL procedure successfully completed.

dbdream@IVLDB> select table_name from user_tab_statistics where stattype_locked is not null; 

no rows selected

dbdream@IVLDB> exec dbms_stats.gather_table_stats('DBDREAM','T_TEST');

PL/SQL procedure successfully completed.

dbdream@IVLDB> select num_rows from user_tables where table_name='T_TEST';

  NUM_ROWS
----------
   2097152

SCHEMA级别的统计信息收集,是不会收集已经锁定统计信息收集的表,而且不会有提示,对于使用SCHEMA级别收集统计信息需要注意。

本文固定链接: http://www.dbdream.com.cn/2017/09/18/ora-20005-object-statistics-are-locked-stattype-all/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2017年09月18日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORA-20005 object statistics are locked (stattype = ALL) | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

ORA-20005 object statistics are locked (stattype = ALL):等您坐沙发呢!

发表评论

您必须 [ 登录 ] 才能发表留言!

13511039874
dbdream