()

Find table’s statistics are LOCKED or UNLOCKED in Oracle

Table’s statistics is a key role in terms of performance along with the execution plan of SQL’s statements. Here we demonstrate how to find the table’s statistics are LOCKED or UNLOCKED in Oracle.

Some time due to performance issues or on-demand business impact or auto-enable statistics by job scheduler or very rarely data changes in the table, we lock statistics on specific tables, in that case, we cannot execute gather statics on a table along with dependent objects of a table. Below are the VALUE of column
STATTYPE_LOCKED which denotes that either table’s statistics are LOCKED or UNLOCKED.


STATTYPE_LOCKED = AUTO ==> Table’s statistics LOCKED
STATTYPE_LOCKED = NULL value ==> Table’s statistics UNLOCKED


Below is the SQL command to find statistics that are LOCKED. Click here for sample output.

COL OWNER FOR A15
COL TABLE_NAME FOR A25
COL STATTYPE_LOCKED FOR A19

SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED FROM DBA_TAB_STATISTICS 
WHERE STATTYPE_LOCKED IS NOT NULL AND TABLE_NAME = 'DBSGURU' ORDER BY 2;

prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$


NOTE
:
 You can also use the below option in case if you don’t want to hardcode OWNER TABLE_NAME in the where clause.

TABLE_NAME=’&TABLE_NAME’


We always encourage the technical person to visit 
section SCRIPTS to get more daily usage SQL commands.

Hope so you like this script!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.



Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

DBsGuru

Hello and welcome to DBsGuru,DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!Share Learn Grow!