DOES DBA_TAB_MODIFICATIONS RECORD DML CHANGES WITHOUT COMMIT?

I just had a thought to test if modifications which happen in a table are recorded in DBA_TAB_MODIFICATIONS dictionary table even though we donot commit a transaction for an object

I insert some records in a table t without commiting the transaction

SQL> insert into test.t select * from test.t;

500000 rows created.

As the changes are not flushed to the disk, i cannot find any changes recorded in the below query

SQL> select TABLE_NAME,
  2         INSERTS,
  3         UPDATES,
  4         DELETES,
  5         TIMESTAMP
  6  from
  7  dba_tab_modifications
  8  where table_name='T'
  9  and TABLE_OWNER='TEST';

no rows selected

I flushed the information to disk

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

After flush, i can able to see the DML changes recorded in the database

SQL> select TABLE_NAME,
  2         INSERTS,
  3         UPDATES,
  4         DELETES,
  5         TIMESTAMP
  6  from
  7  dba_tab_modifications
  8  where table_name='T'
  9  and TABLE_OWNER='TEST';

TABLE_NAME              INSERTS    UPDATES    DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
T                        500000          0          0 29-JAN-22

I rollback the transaction instead of committing it.

SQL> rollback;

Rollback complete.

After i rollback the transaction, what i observed is the changed data become invisible again as i didnt find any results from the below query

SQL> select TABLE_NAME,
  2         INSERTS,
  3         UPDATES,
  4         DELETES,
  5         TIMESTAMP
  6  from
  7  dba_tab_modifications
  8  where table_name='T'
  9  and TABLE_OWNER='TEST';

no rows selected

This means that after the transaction is rollback, oracle used the undo data to revert back the changes and removed the entry from dba_tab_modifications

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s