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