Lock and unlock table stats

Some times table statistics might be locked.

If you try to gather stats in lock  state you will get error like this

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 2

So to unlock it here is the query you need to use

exec dbms_stats.unlock_table_stats('HMA_TM_PROD_GEN2_MONITOR','ZZT_SERVICE_LOG');

Now stats gathering will go fine.

To relock again

exec dbms_stats.lock_table_stats('HMA_TM_PROD_GEN2_MONITOR','ZZT_SERVICE_LOG');


Unlock all table stats in schema

select 'exec dbms_stats.unlock_table_stats (''HMA_STAGE'', '''||table_name||''');' from dba_tables where owner='HMA_STAGE';