How to find out the table was last modified in Oracle?

In my previous article i have given idea about the how to find out backup of table? and questions like that. In this article i would like to give answer of one of the interview question asked to DBA – How to find out the table was last modified in Oracle? There are so many times the developers or DBA need to check who has updated or modified the table.Modification meaning here is nothing but insert,update and delete.We require to use the dictionary table in oracle named dba_tab_modifications.

How to find out the table was last modified in oracle? – Real life Scenario

Step 1 : Insert the data in customer table.

Insert into Customer

values(1,’Amit);

Commit;

Step 2 : We require to check the information in dba_tab_modifications

The following query will give you information about the

select INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP from dba_tab_modifications where TABLE_NAME=’Customer’ and TABLE_OWNER=’Amit’;

Output :

no rows found

If you can see the output the rows are not found. The next step is you require to flush the information and update that information in dba_tab_modification table.

Step 3 : Flush the information and Monitor it

3.Flush the monitoring Information

In this step we require to execute the flush statement of oracle.

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; 

PL/SQL procedure successfully

select INSERTS,UPDATES,DELETES,TRUNCATED,TIMESTAMP from dba_tab_modifications where TABLE_NAME=’Customer’ and TABLE_OWNER=’Amit’;

Output :

INSERTS    UPDATES    DELETES TRU TIMESTAMP

———- ———- ———- — ————————————

1           0           0 NO  15-AUG-20 

You can see that there is one insert done on customer table. You can also see the timings by which time the insert or update has happened or not.

  select owner,object_name,object_type,status,last_ddl_time from dba_objects where object_name=’Amit’ and object_type=’TABLE’;

Hope you can use these queries to get information about update,insert or delete time or modification time for that table.