Skip to Main Content
  • Questions
  • How to find last DDL / DML timestamp for IOT tables

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hariharan.

Asked: January 23, 2020 - 1:30 pm UTC

Answered by: Chris Saxon - Last updated: January 27, 2020 - 1:58 pm UTC

Category: Database Administration - Version: 12.1.0.2

Viewed 100+ times

You Asked

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..



and we said...

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.

BUT

A 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.

and you rated our response

  (2 ratings)

Reviews

January 24, 2020 - 12:22 pm UTC

Reviewer: A reader

Please check the below values in our Customer database.

SQL> select min ( time_dp ), min ( scn_bas ) from sys.smon_scn_time ;

MIN(TIME_ MIN(SCN_BAS)
--------- ------------
19-JAN-20 724824214


SQL> select max(ora_rowscn) from SCHEMA.TABLE;

MAX(ORA_ROWSCN)
---------------
9484035231

So, the ora_rowscn is not below the SCN value.

now can you tell me how to find the last DML time stamp for this table?


Thanks,

Chris Saxon

Followup  

January 27, 2020 - 1:58 pm UTC

Something doesn't add up with those numbers: the min SCN around eight days ago was ~720 MILLION. The ORA_ROWSCN is now ~9.4 BILLION.

Meaning the SCN has increased ~ 9 billion around a week. i.e. in the past week there's been 10x more commits than the entire history of the application.

Seems unlikely to me.

But I reiterate:

Forget this approach

Enable auditing NOW. Review in a few weeks/months/years to see what (if any) activity there is on these tables.

February 05, 2020 - 2:14 pm UTC

Reviewer: A reader

Thanks,

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.