链接：http://blog.itpub.net/28602568/viewspace-2077883/

标题：ORA-20005: object statistics are locked (stattype = ALL)

注释：

impdp 导入数据库时，到最后STATISTICS部分异常断开，之后再在DB层通过DBMS_STATS.gather_schema_stats手动统计用户时执行很快，检查表信息并未真正统计，再通过 DBMS_STATS.gather_table_stats单独通过表时报对象统计信息被锁住，详情如下。

报错复现：

SQL> EXEC DBMS_STATS.gather_table_stats('U1','BASE_ROLE',cascade=>True,no_invalidate=>false);

begin DBMS_STATS.gather_table_stats('U1','BASE_ROLE',cascade=>True,no_invalidate=>false); end;

ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: 在 "SYS.DBMS_STATS", line 23829
ORA-06512: 在 "SYS.DBMS_STATS", line 23880
ORA-06512: 在 line 1

SQL>

*** 根据报错，通过user_TAB_STATISTICS查看当前用户下对象的lock状态，再通过存储dbms_statS.unlock_table_stats 解锁。

查看锁情况：

SQL> SELECT D.TABLE_NAME,STATTYPE_LOCKED FROM USER_TAB_STATISTICS D WHERE D.TABLE_NAME = 'BASE_ROLE';

TABLE_NAME STATTYPE_LOCKED

----------------------------- ---------------

BASE_ROLE ALL

锁状态为all，如下对BASE_role表unlock，并再次检查锁状态；

SQL> execute dbms_statS.unlock_table_stats(ownname=>'U1',tabname =>'BASE_ROLE');

PL/SQL procedure successfully completed

SQL> SELECT D.TABLE_NAME,STATTYPE_LOCKED FROM USER_TAB_STATISTICS D WHERE D.TABLE_NAME = 'BASE_ROLE';

TABLE_NAME STATTYPE_LOCKED

------------------------------ ---------------

BASE_ROLE

SQL>

dbms_stats.unlock_table_stats存储的介绍

procedure unlock_table_stats(ownname varchar2,tabname varchar2,stattype varchar2 default 'ALL');

-- This procedure enables the user to lock the statistics of all

-- tables of a schema

-- Input arguments:

-- ownname - schema of tables to lock

-- stattype - type of statistics to be locked

-- 'CACHE' - lock only caching statistics

-- 'DATA' - lock only data statistics

-- 'ALL' - lock both data and caching statistics. This is the default

解锁用户：execute dbms_statS.unlock_schema_stats(ownname=>'U1');

【源于本人笔记】若有书写错误，表达错误，请指正...

此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。

ITPub博客

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

最新文章