How can I check the last time stats was run on Oracle without using OEM

oracle oracle10g sqlplus statistics
Jacob picture Jacob · Mar 3, 2010 · Viewed 96k times · Source

I want to check the last time stats was run on my Oracle 10g server. I would normally do this via OEM, but for unrelated reasons OEM is down. Is there some way I can check this using just sqlplus? It would be extra helpful if the output was reasonably formatted.

Answer

Adam Musch picture Adam Musch · Mar 3, 2010

All of the following data dictionary tables have a LAST_ANALYZED column (replace * with USER/ALL/DBA as appropriate:

*_TABLES
*_TAB_PARTITIONS
*_TAB_SUBPARTITIONS
*_INDEXES
*_IND_PARTITIONS
*_IND_SUBPARTITIONS

(There's lots more in the histograms fields, but I'm not going that deep.)

Conversely, ALL_TAB_MODIFICATIONS shows rows inserted/updated/deleted (or the timestamp on which a table/partition/subpartition was truncated) since it had optimizer statistics gathered.

Related questions

How to kill a running SELECT statement

How can I stop a running SELECT statement by killing the session? The command is continuously giving me output based on the SELECT statement, I want to stop it in between.

Read More
How to View Oracle Stored Procedure using SQLPlus?

How can I view the code of a stored procedure using sqlplus for Oracle 10g? When I type in: desc daily_update; it shows me the parameter, but when I try to do the following: select * from all_source where …

Read More
Ugly formatting in SQL*Plus

It is really annoying that when I run a select command in SQL*Plus such as: SELECT * FROM books; The output is really badly formatted and unreadable (row cells are not in a row but separated by line breaks etc): …

Read More