Solved

Script to fild stale table stats in database

Posted on 2007-12-05
5
1,211 Views
Last Modified: 2011-10-03
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
Comment
Question by:jkopoku
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 125 total points
ID: 20413419
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
 

Author Comment

by:jkopoku
ID: 20413766
I want to know all tables with >10% row change.
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 125 total points
ID: 20416123
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20936944
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question