How to Find Last Analyzed date on Tables, Partition, and Subpartition in Oracle

Find Last Analyzed date on Tables, Partitions, and Subpartitions in Oracle

Here we are going to demonstrate how to find the last analyzed date on tables in Oracle. Some time due to performance issues, statistics locked on the table, disable statistics in the database, before/after manual analyze on tables or gather statistics, before/after migration, etc. We need to know the last analyzed of tables, partitions, and subpartitions, below is SQL commands for the same.

SQL query to find LAST_ANALYZED date for TABLES, click here for sample output.

SET LINES 333 PAGES 222
COL OWNER FOR A11
COL TABLE_NAME FOR A15
COL GLOBAL_STATS FOR A13

SELECT OWNER,TABLE_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'DD-MON-YYYY:HH24:MI:SS') LAST_ANALYZED,GLOBAL_STATS,STATUS FROM DBA_TABLES 
WHERE OWNER='TBLUSR' AND TABLE_NAME='MOTORFOTONG' ORDER BY OWNER,TABLE_NAME,LAST_ANALYZED;
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.

OWNER=’&OWNER’

TABLE_NAME=’&TABLE_NAME’

SQL query to find LAST_ANALYZED date for PARTITIONED TABLES, click here for sample output.

SET LINES 333 PAGES 222
COL OWNER FOR A11
COL TABLE_NAME FOR A15
COL GLOBAL_STATS FOR A13
COL PARTITION_NAME FOR A15
COL TABLE_OWNER FOR A15

SELECT TABLE_OWNER,TABLE_NAME, PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'DD-MON-YYYY:HH24:MI:SS') LAST_ANALYZED,GLOBAL_STATS FROM DBA_TAB_PARTITIONS 
WHERE TABLE_OWNER='TBLUSR' AND TABLE_NAME='MOTORFOTONG' ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

SQL query to find LAST_ANALYZED date for SUBPARTITIONED TABLES, click here for sample output.

SET LINES 333 PAGES 222
COL OWNER FOR A11
COL TABLE_NAME FOR A23
COL GLOBAL_STATS FOR A13
COL PARTITION_NAME FOR A15
COL SUBPARTITION_NAME FOR A19
COL TABLE_OWNER FOR A15

SELECT TABLE_OWNER,TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'DD-MON-YYYY:HH24:MI:SS') LAST_ANALYZED,GLOBAL_STATS FROM DBA_TAB_SUBPARTITIONS 
WHERE TABLE_OWNER='TBLUSR' 
AND TABLE_NAME='MOTORFOTONG' 
AND ROWNUM<11 ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,LAST_ANALYZED;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

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.

 245 total views,  4 views today

DBsGuru

Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and its 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!

dbsguru.com/