Query to Check date and time of Last DML done on a table in oracle

As per the requirement i need to capture the the last DML operations in tables in complete schema while searching i have noticed two resources

Resource 1 https://dba.stackexchange.com/questions/115062/how-to-get-a-last-dml-operation-in-oracle-10g-11g

Resource 2 https://oraclebykkbakshi.blogspot.com/2018/04/check-date-and-time-of-last-dml-done-on.html

Among the above i have tried using both the queries

Query1 - DBA_TAB_MODIFICATIONS

SELECT TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP AS LAST_CHANGE
FROM  DBA_TAB_MODIFICATIONS
WHERE TO_CHAR(TIMESTAMP,'DD.MM.YYYY') = TO_CHAR(sysdate,'DD.MM.YYYY') 
and table_owner='SCHEMA_NAME'
and table_name in ('MY LIST OF TABLES')

Result - it fetched only the results which had fetched only few tables

Query2 - is very slow

select max(ora_rowscn),SCN_TO_TIMESTAMP(max(ora_rowscn)) FROM 'MY LIST OF TABLES'

If i check for any tables using query 2 i am getting the below error

Error Query 2

ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
08181. 00000 -  "specified number is not a valid system change number"
*Cause:    supplied scn was beyond the bounds of a valid scn.
*Action:   use a valid scn.

how to check all the tables in a single go ,since if i need to use query 2 ,how do i list out multiple tables in a single query ?

so my question is which is the correct query to search for ?

or any other better ways to look for ,any suggestions ?

Upon suggestion i have tried running the query

SELECT *
  FROM 'TABLE_NAME' t
MINUS
SELECT *
  FROM 'TABLE_NAME'
  AS OF timestamp systimestamp - interval '1' day t

AS OF timestamp systimestamp - interval '1' day t - what is the value i have to use here ?

Solution

You might use

AUDIT clause for an individual schema such as hr
```
AUDIT ALL BY hr BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY hr BY ACCESS;
```
after connectiong to SYS as admin, in order to capture the DML.

Or, alternatively you can create

some database triggers for some desired tables, of which you need to track the DML data in order to retain the log within a customly created new table(s).

Or for the current position for querying the previously processed data, you can use

Flashback Queries having

AS OF timestamp systimestamp - INTERVAL '<int>' [year|month|day|hour|minute|second] syntax for an individual table in such a way that
```
SELECT *
  FROM tab t
 WHERE t.id = :v_id
MINUS
SELECT *
  FROM tab
  AS OF timestamp systimestamp - interval '1' day t
 WHERE t.id = :v_id;
```
which returns the data of the day before having the same hour:minute:second combination with the current time provided the value of undo_retention DB paremeter is big enough to hold the data of the time point in the past. (For this case, the value should be 86400(in second) at least)