Check How Much Table data has Changed Since the Last Analyze
--
-- Check How Much Table data has Changed Since the Last Analyze.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
 
-- *************************************************************************
-- * Enter a Valid Table Owner and a Valid Table Name.  (% for ALL Tables).
-- * Change Factor = High the Number, Greater the Change
-- *************************************************************************
 
COL table_name FOR A30
COL table_owner FOR A20
 
SELECT *
  FROM ( SELECT m.table_owner
              , m.table_name
              , t.last_analyzed
              , m.inserts
              , m.updates
              , m.deletes
              , t.num_rows
              , ( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END "Change Factor"
           FROM dba_tab_modifications m
              , dba_tables t
           WHERE t.owner = m.table_owner
             AND t.table_name = m.table_name
             AND m.inserts + m.updates + m.deletes > 1
             AND m.table_owner='&Enter_Table_Owner'
             AND m.table_name like '&Enter_Table_Name'
           ORDER BY "Change Factor" DESC
       )
/

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License