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

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

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?

Spread the love

Leave a Reply

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

eleven − 5 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru