How to check the last DML time on object

How to check the last update time on object

1) select * from all_tab_modifications where table_name = '<table_name>'; ( may not be accurate)

2) select to_char(scn_to_timestamp(max(ora_rowscn))) from <table_name>; ( available from 10g).


SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;

Min value that you can convert is :(oldest available SCN).
SQL> select min(SCN) min_scn from sys.smon_scn_time;

           MIN_SCN
------------------
    10346369869923

*** The values of ORA_ROWSCN available are constrained by the contents of SYS.SMON_SCN_TIME.

If you give try to convert the value which less than this value:

SQL> select  scn_to_timestamp(10346369869922) from dual;

select  scn_to_timestamp(10346369869922) from dual

        *

ERROR at line 1:

ORA-08181: specified number is not a valid system change number

ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

To check the current change number:

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER

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

          10347141818837

SQL>  select  scn_to_timestamp(10346369869924) from dual;

SCN_TO_TIMESTAMP(10346369869924)

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

11-SEP-15 09.31.02.000000000 PM

TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamp value. You must do to_timestamp convertion for the character value.

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;
SCN
———-
845396