Statistics played in key role in Oracle performance tuning. Oracle Optimizer creates the execution plan based on the statistics of the Oracle table involved in the SQL queries.

You may want to lock statistics in an Oracle table in some cases, for example

- you don’t want a table to be analyzed by a scheduled statistics job but want to analyze it later or at a higher estimate
- you don’t want to generate the statistics for the table for performance reason
- you don’t want the server to spend time generating statistics when the table data doesn’t change

There could be many more cases where we want to lock statistics

## How to lock statistics on the table

You can use the standard Oracle package DBMS_STATS to lock the statistics on the table

exec dbms_stats.lock_table_stats('table_owner','table_name');

ExampleSELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'TECH'; STATTYPE_LOCKED —–------------ exec dbms_stats.lock_table_stats('TEST','TECH'); SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'TECH'; STATTYPE_LOCKED —–-------- ALL

## How to lock statistics on the schema

You can use the standard Oracle package DBMS_STATS to lock the statistics on the schema

exec dbms_stats.lock_schema_stats('<schema name>');

## How to lock statistics on the table partition

exec dbms_stats.lock_partition_stats('<table_owner>', '<table_name>', '<partition_name>');

**How to find the table where statistics are locked** in Oracle

You can use the below query to find all the tables where statistics is locked

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

## Running stats generation Job on the table where statistics is locked

If we try to run gather statistics on the tables where statistics is locked, we get **ORA-20005 object statistics are locked (stattype = all)**

SQL> exec dbms_stats.gather_table_stats('TECH', 'TEST'); BEGIN dbms_stats.gather_table_stats('TECH', 'TEST'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at “SYS.DBMS_STATS”, line 10640 ORA-06512: at “SYS.DBMS_STATS”, line 10664 ORA-06512: at line 1

We can perform the below steps to unlock the statistics, generate the statistics and lock them again

exec dbms_stats.unlock_table_stats('TECH','TEST'); exec dbms_stats.gather_table_stats('TECH', 'TEST'); exec dbms_stats.lock_table_stats('TECH','TEST'); or exec dbms_stats.gather_table_stats('TECH', 'TEST',force=>true);

## How to unlock statistics for Table and schema /unlock table stats for schema

Now once we find the objects, we can use the below queries to unlock them

unlock schema statsexec dbms_stats.unlock_schema_stats('schema_owner');Unlock table statsexec dbms_stats.unlock_table_stats('table_owner','table_name');Unlock table partition statsexec dbms_stats.unlock_partition_stats('table_owner','table_name','partition name');Exampleexec dbms_stats.unlock_schema_stats('TECH'); exec dbms_stats.unlock_table_stats('TECH','TEST');

### Index creation with statistics locked on the table

10g onwards, whenever we create an index, the statistics are generated automatically. Now this equation changes When the table is locked, statistics will not be generated while creating the index. We need to use the FORCE option to gather the statistics while creating an index for locked objects.Let’s understand that in detail by seeing the example

ExampleLets first create the dummy table and lock the statistics on that tableSQL> create table test as select a.* ,rownum id from all_objects a where rownum <1001; SQL> exec dbms_stats.lock_table_stats('TECH','TEST');Now we will try to create indexSQL> create index test_idx on test(id); Index created. SQL> select num_rows, last_analyzed from user_ind_statistics where index_name ='TEST_IDX'; NUM_ROWS LAST_ANAL ---------- ---------So statistics on index is not generated automatically for the locked statistics tableLets try to generate the statistics using DBMS_STATSSQL> exec dbms_stats.gather_index_stats(null, 'TEST_IDX'); BEGIN dbms_stats.gather_index_stats(null, 'TEST_IDX'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 10640 ORA-06512: at "SYS.DBMS_STATS", line 10664 ORA-06512: at line 1So statistics generation failed.In order to generate stats on the index, We can use force option in dbms_stats to override thisSQL> exec dbms_stats.gather_index_stats(null, 'TEST_IDX',force=>true); PL/SQL procedure successfully completed. SQL> select num_rows, last_analyzed from user_ind_statistics where index_name ='IDX'; NUM_ROWS LAST_ANAL ---------- --------- 1000 01-SEP-17Lets try to create a new index with compute statistics clauseSQL> create index TEST_IDX1 on test(object_name) compute statistics; create index idx on test(object_name) compute statistics * ERROR at line 1: ORA-38029: object statistics are lockedSo ORA-38029 error happens, So we need to create index with out the compute statistics clause and then generate stats using force optionSQL> create index TEST_IDX1 on test(object_name); SQL> exec dbms_stats.gather_index_stats(null, 'TEST_IDX1',force=>true);Same things happens if we rebuild the index with compute statistics optionSQL> alter index TEST_IDX1 rebuild compute statistics; alter index TEST_IDX1 rebuild compute statistics * ERROR at line 1: ORA-38029: object statistics are locked SQL> alter index TEST_IDX1 rebuild; Index altered. SQL> exec dbms_stats.gather_index_stats(null, 'TEST_IDX1',force=>true); PL/SQL procedure successfully completed.

Hope you like the information on how to lock/unlock table statistics in Oracle,How to find table where statistics are locked. Also Now you must know what to do when **ORA-20005: object statistics are locked** and **ORA-38029: object statistics** are locked happens

**Related Articles**

Gathering Statistics in Release 11i and R12

Incremental Statistics Gathering in 11g

ora-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)

How to set Table Monitoring in Oracle and Relationship with STATISTICS_LEVEL

How to check Stale statistics in Oracle

Oracle Optimizer Mode

Oracle documentation on statistics