DBA_TAB_MODIFICATION

DBA_TAB_MODIFICATION view does not immediately populated after the modification happen on the table. FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_STATS  package can be used to populate until recent information. Also to add, ANALYZE_ANY system privilege is required to run this procedure.

Below is the test study.

SQL> create table test29 as select * from test23;

Table created.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='TEST29';

no rows selected

SQL> select * from test29;

         X

----------

    100000

9999999999

SQL> insert into test29 values (2);

1 row created.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='TEST29';

no rows selected

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='TEST29';

TABLE_NAME                        INSERTS    UPDATES    DELETES

------------------------------ ---------- ---------- ----------

TEST29                                  1          0          0

SQL> select * from test29;

         X

----------

    100000

9999999999

         2

SQL> delete from test29 where x=2;

1 row deleted.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='TEST29';

TABLE_NAME                        INSERTS    UPDATES    DELETES

------------------------------ ---------- ---------- ----------

TEST29                                  1          0          0

SQL> commit;

Commit complete.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='TEST29';

TABLE_NAME                        INSERTS    UPDATES    DELETES

------------------------------ ---------- ---------- ----------

TEST29                                  1          0          0

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications where table_name='TEST29';

TABLE_NAME                        INSERTS    UPDATES    DELETES

------------------------------ ---------- ---------- ----------

TEST29                                  1          0          1

SQL>