• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to find table where statistics are locked

How to find table where statistics are locked

December 20, 2020 by techgoeasy Leave a Comment

Statistics played in key role for 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 a oracle table in some cases, for example

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

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

Table of Contents

  • How to lock statistics on the table
    • How to find table where statistics are locked
    • Running stats generation Job on the table where statistics is locked 
    • How to unlock statistics for Table and schema /unlock table stats for schema
    • Index creation with statistics locked on table

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');
Example

SELECT 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 find table where statistics are locked

You can use 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 below steps to unlock the statistics and generate the statistics and lock 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 out the objects,we can use below queries to unlock them

unlock table stats for schema
exec dbms_stats.unlock_schema_stats('schema_owner');

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


Example

exec dbms_stats.unlock_schema_stats('TECH');
exec dbms_stats.unlock_table_stats('TECH','TEST');

Index creation with statistics locked on table

10g onwards, whenever we create 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 FORCE option to gather the statistics while creating index for locked objects.Lets understand that in details by seeing the example

Example

Lets first create the dummy table and lock the statistics on that table

SQL>  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 index

SQL> 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 table

Lets try to generate the statistics using DBMS_STATS

SQL> 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 1

So statistics generation failed.

In order to generate stats on the index, We can use force option in dbms_stats to override this

SQL> 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-17

Lets try to create a new index with compute statistics clause

SQL> 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 locked

So ORA-38029 error happens, So we need to create index with out the compute statistics clause and then  generate stats using force option

SQL> 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 option

SQL> 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 tables statistics in oracle. Also Now you must be 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
Oracle Tutorial: How to check Stale statistics
Oracle Optimizer Mode
Oracle documentation on statistics

Filed Under: Oracle, Oracle Database Tagged With: dba_tab_statistics, dbms_stats.unlock_schema_stats, How to find table where statistics are locked, ORA-20005, ORA-38029, oracle unlock statistics

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us