Locking statistics for a table

You can lock the statistics for the table if you don’t want it be analyzed automatically by scheduled jobs.

exec dbms_stats.lock_table_stats(‘&owner’,’&table name’);
exec dbms_stats.lock_table_stats(‘GOLD’,’TEST’);

To list all tables with locked statistics in a particular schema 

select table_name, stattype_locked from dba_tab_statistics where owner =’&owner’ and stattype_locked is not null;

To unlock the stats of a particular table

exec DBMS_STATS.UNLOCK_TABLE_STATS(‘&owner’,’&table name’);

To unlock the tables with locked stats of a particular schema

select ‘exec DBMS_STATS.UNLOCK_TABLE_STATS (”’|| owner ||”’,”’|| table_name ||”’);’ from dba_tab_statistics where owner = ‘&owner’ and stattype_locked is not null;

when stats is not locked the value of stattype_locked is NULL and  ALL when stats is locked

SQL> SELECT TABLE_NAME , stattype_locked FROM dba_tab_statistics where owner = ‘GOLD’;
TABLE_NAME                STATT
—————————— —–
TEST                            ALL < — LOCKED
TEST1                                      <  — UNLOCKED