Hi,
One of my Customer is asking for last DDL/DML timestamp change for IOT tables. They have many tables and they need to drop some of them which is not used / modified / altered frequently. I checked internally for IOT and Normal tables using ORA_ROWSCN but for them its not possible getting (ORA-08181) and the Tables also old.
1. How to get the last DDL/DML timestamp for IOT tables. ( Primary issue )
2. If we fix this Error may be they can use ora_rowscn query. ( Secondary )
SQL> select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from report.PUBURL_INCEPTION_0;
select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from report.PUBURL_INCEPTION_0
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1.Kindly help us.
Thanks in advance..
The last DDL time is easy:
select last_ddl_time
from user_objects
where object_name = :tab;
As you're finding, if you've not got auditing, last DML write time is a little trickier...
You can find the lowest SCN -> time mapping by querying:
select min ( time_dp ), min ( scn_bas )
from sys.smon_scn_time ;
If the ora_rowscn is below this SCN value and you've then you're out of luck. Comparing this minimum to the ora_rowscn will tell you that the table hasn't changed in at least this long though.
BUTA table that was last changed ages ago
may still be used!
The above doesn't track queries. Reference tables can often stay unchanged for months or years. But are still core to the application working.
If you want to see if a table is accessed, enable auditing for "a while" to see if anything is using it.