• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1230
  • Last Modified:

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.
0
jkopoku
Asked:
jkopoku
2 Solutions
 
Jinesh KamdarCommented:
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 KumarProduction 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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now