How to Lock and Unlock Stats in Oracle Database

<<Back to PT Main Page

How to Lock Statistics of a Table

If due to some reasons you decided to lock the status of the table you can do it as follows.

Note: Once you lock the statistics oracle no more calculates stats for those objects.

To lock the table status use DBMS_STATS.LOCK_TABLE_STATS procedure. In the following example I have locked the status of EMP table in HR schema

BEGIN
DBMS_STATS.LOCK_TABLE_STATS ('HR','EMP');
END;
/

How Find the Table for Which Stats are Locked

Run below query to check if the stats for the table is locked

SQL> SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED FROM DBA_TAB_STATISTICS WHERE STATTYPE_LOCKED IS NOT NULL AND TABLE_NAME='EMP';
OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
HR                     EMP                           ALL

Now if you run stats collection for this table you will witness following error

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','EMP');
exec DBMS_STATS.gather_table_stats('HR','EMP'); END;
*
ERROR at line 1:
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 1

How to Unlock Statistics of a Table

To unlock the table stats use following procedure

EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('HR','EMP');
or
EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('HR','EMP',force=>true);

How to lock Statistics of an Schema

Use the following procedure to lock the whole schema stats
EXEC DBMS_STATS.LOCK_SCHEMA_STATS ('HR');

How to Unlock Statistics of an Schema

Use the following procedure to unlock the schema stats.

SQL> EXEC DBMS_STATS.UNLOCK_SCHEMA_STATS ('HR');