在手工更新统计信息时,报ORA-20005异常 SQL>exec dbms_stats.gather_table_stats('SYS','TEST'); BEGINdbms_stats.gather_table_stats('SYS','TEST'); END; * ERRORat 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 异常原因:查询test表统计信息stattype_locked值,发现统计信息被锁定 SQL> select owner,table_name,stattype_locked from dba_tab_statistics a where a.stattype_locked in ('ALL','DATA','CACHE') and table_name='TEST';
OWNER TABLE_NAME STATTYPE_L --------------- ------------------------------ ---------- SYS TEST ALL 处理方案:将统计信息解锁,然后在执行更新 SQL>exec dbms_stats.unlock_table_stats(ownname => 'SYS',tabname => 'TEST'); PL/SQLprocedure successfully completed. SQL>exec dbms_stats.gather_table_stats('SYS','TEST'); PL/SQL procedure successfully completed.
注:stattype_locked为空代表统计信息未锁定,为all表示表的统计信息被锁
|