Tuesday, August 25, 2015

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

Getting Oracle errors stating object statistics are locked. This is severe enough to cause vCenter server to go down sporadically.

2015-08-11T12:57:01.838-14:32 [08712 error 'Default' opID=XXXXXXXXXXX] [VdbStatement] SQLError was thrown: "ODBC error: (HY000) - [Oracle][ODBC][Ora]ORA-20005: object statistics are locked (stattype = ALL)
--> ORA-06512: at "SYS.DBMS_STATS", line 23429
--> ORA-06512: at "SYS.DBMS_STATS", line 23480
--> ORA-06512: at line 1
--> " is returned when executing SQL statement "BEGIN dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125'); END;"



Simulating the fix


VPXADMIN55> exec dbms_stats.lock_table_stats('VPXADMIN55','vpx_hist_stat1_125');

PL/SQL procedure successfully completed.

VPXADMIN55> exec  dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125');
BEGIN dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125'); END;

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


VPXADMIN55> exec dbms_stats.unlock_table_stats('vpx_hist_stat1_125');
BEGIN dbms_stats.unlock_table_stats('vpx_hist_stat1_125'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'UNLOCK_TABLE_STATS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


VPXADMIN55>  exec dbms_stats.unlock_table_stats('VPXADMIN55','vpx_hist_stat1_125');

PL/SQL procedure successfully completed.

VPXADMIN55>  exec  dbms_stats.gather_table_stats(null, 'vpx_hist_stat1_125');

PL/SQL procedure successfully completed.

VPXADMIN55>



This is not a common practice with vCenter database. I suspect someone might have put a lock on the table statistics for unknown reasons.

No comments:

Post a Comment