公司收购了石家庄一家公司，要把客户的数据迁移到我们的数据库中，昨晚加班，进行迁移测试演练，石家庄那面的数据库版本是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/ora-20005-object-statistics-are-locked-stattype-all/ | 信春哥，系统稳，闭眼上线不回滚！