- 17 Dec, 2023
- 0 Comments
- 2 Mins Read
ORA-20005: Object Statistics Are Locked (Stattype = ALL)
Solution :
If stats are locked for a table or schema, then gathering stats will be fail with ORA-20005 error. Unlock stats and run gather stats.SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='TEST' and owner='LEARNOMATE'; OWNER TABLE_NAME STATTYPE_LOCKED ---------- ---------- ---------------------------------- LEARNOMATE TEST ALL
Unlock stats:
SQL> SQL> EXEC DBMS_STATS.unlock_table_stats('LEARNOMATE','TEST');
PL/SQL procedure successfully completed.
SQL> SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='TEST' and owner='LEARNOMATE';
OWNER TABLE_NAME STATTYPE_LOCKED
---------- ---------- ----------------------------------
LEARNOMATE TEST
Now try to run stats again:
SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS ( 3 ownname => 'LEARNOMATE', 4 tabname => 'TEST', 5 cascade => true, ---- For collecting stats for respective indexes 6 method_opt=>'for all indexed columns size 1', 7 granularity => 'ALL', 8 estimate_percent =>dbms_stats.auto_sample_size, 9 degree => 8); 10 END; 11 / PL/SQL procedure successfully completed.Similarly we can unlock stats for a schema also.
SQL> EXEC DBMS_STATS.unlock_schema_stats('LEARNOMATE');
PL/SQL procedure successfully completed.