Lock and Unlock table stats in oracle database

There are unlimited features provided by oracle with an upgraded version, one of the feature we are going to discuss is stats lock and unlock in the oracle database,

Assume the application team has set up stats gather job from application end but that causing issue to the database like CPU/Memory utilization getting high during the job execution, being a DBA you can ask them to stop/disable the job but in case if it’s not feasible then you can lock stats on tables and unlock whenever required.

Another scenario- you have locked a good execution plan for ETL/Job run and that should not change with stats gathered, then you can lock the stats for the table.

Below we will discuss how to do this exactly, I have a table called LOCATIONS under user TECHNO, I do not want to gather stats on the same, in the below query, if stats are locked, the output will be ALL if not then it will be blank, you can stats are not locked

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

 

 

SQL>

Now I will lock stats and execute the query again

SQL> exec dbms_stats.lock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'LOCATIONS' and owner = 'TECHNO';

 

STATT

-----

ALL

 

SQL>

Now I will try to gather stats on the same table, it should throw error 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

BEGIN dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS'); END;

 

*

ERROR at line 1:

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

ORA-06512: at "SYS.DBMS_STATS", line 40751

 [Also Read - All About Database links]

Now we will see how to unlock stats and gather stats

SQL> exec dbms_stats.unlock_table_stats('TECHNO','LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats(ownname=> 'TECHNO', tabname=>'LOCATIONS');

 

PL/SQL procedure successfully completed.

 

SQL>