Skip to Main Content
  • Questions

  • How to get the last DML Operation time on all the tables of a Schema

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, vinesh.

Asked: August 24, 2021 - 3:07 pm UTC

Last updated: August 25, 2021 - 4:56 pm UTC

Version: Oracle Database 11g Release 11.2.0.4.0 - 64bit

Viewed 10K+ times! This question is

You Asked

Team,

Let's consider the scott schema for our reference.

Can someone please share a query where in we can get the last DML operation performed on all the tables of scott schema.

Please do let me know if we can use ALL_TAB_MODIFICATIONS table's Timestamp column.

In case yes, Please let us know in case of any restrictions on using this table.



Thank you !!

and Chris said...

As the docs say about this view:

ALL_TAB_MODIFICATIONS describes tables accessible to the current user that have been modified since the last time statistics were gathered on the tables.

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/ALL_TAB_MODIFICATIONS.html#GUID-97603337-F3D8-4FC4-ADE0-E49200F6EE4E

So that would be a no!

If you want to capture details about what statements were executed when, you should enable auditing.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.