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

       )

/