Script to fild stale table stats in database

I have about 500 tables in different schemas. I need a script that can help me identify the tables with stale statistics. Any help would be greatly appreciated.
jkopokuAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jinesh KamdarConnect With a Mentor Commented:
Dictionary tables can provide u with info. as to when was the object last analyzed. However, it would be ur call to define an object as "stale" i.e. would it be a week old or a month old or a year old. For e.g.

SELECT table_name, num_rows, last_analyzed
FROM user_tables;
0
 
jkopokuAuthor Commented:
I want to know all tables with >10% row change.
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
We use this query  :

select  ---*
table_name, partition_name, object_type
from user_tab_statistics  -- you can even use dba_tab_statistics
where stale_stats = 'YES';

Thanks
0
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.