How to find table’s statistics are LOCKED or UNLOCKED in Oracle

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.

• WordPress
• Facebook
• LinkedIn
• Twitter
• Instagram
• Telegram
• Mail