Last DDL and DML Date/Time of Any Table

 Get Last DDL and DML Date Time Of Any Table.

 

SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.
 

SQL>  select (select last_ddl_time from dba_objects where object_name='EMP' and owner='SHAAN') "DDL Time",
decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
from (select nvl(max(ora_rowscn),0) maxscn from SHAAN.EMP);   2    3

DDL Time             DML Time
-------------------- ----------------------------------------
05-OCT-2022 22:30:16 05-OCT-22 10.31.21.000000000 PM

SQL> col "Owner Object" for a30
set lines 200 pages 1000
 select (select owner||'.'||object_name from dba_objects where object_name='EMP' and owner='SHAAN') "Owner Object",
 (select created from dba_objects where object_name='EMP' and owner='SHAAN') "Created Time",
 (select last_ddl_time from dba_objects where object_name='EMP' and owner='SHAAN') "DDL Time",
 decode(maxscn,0,'N/A',scn_to_timestamp(maxscn)) "DML Time"
 from (select nvl(max(ora_rowscn),0) maxscn from SHAAN.EMP);SQL> SQL>   2    3    4    5

Owner Object                   Created Time         DDL Time             DML Time
------------------------------ -------------------- -------------------- ----------------------------------------
SHAAN.EMP                      05-OCT-2022 22:30:16 05-OCT-2022 22:30:16 05-OCT-22 10.31.21.000000000 PM