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.
