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
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!