[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Script to fild stale table stats in database

Posted on 2007-12-05
5
Medium Priority
?
1,228 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
4 Comments
 
LVL 18

Accepted Solution

by:
Jinesh Kamdar earned 500 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 500 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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 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 video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month20 days, 2 hours left to enroll

873 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