Solved

Script to fild stale table stats in database

Posted on 2007-12-05
5
1,207 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
case statement in where clause 5 48
Oracle Pivot Question 8 62
exp/imp 25 75
PL SQL Developer 7 35
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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