How to find idle tables last month or last week

Question

I have more the 100 table in my project. Every one hour millions of records insert in my database in this case. How find unused (idle) tables last one month or last one week. How get alert message.

Technology – Java/J2ee Database – Oracle

Answer 1

Can't do it unless every single row has an INSERT and/or UPDATE timestamp.

OR create a separate table that keeps track of the name of each table and the timestamp for the last activity. You can update this using a trigger.

If they do, you just do a query to find the most recent timestamp and compare it to the current time. If it exceeds your "idle" time, make note of the time and have your middle tier send an alert e-mail.

Since you're using Oracle, you can also turn on CDC (Change Data Capture) and use its features.

It all hinges on the timestamp. If you don't have it, add one.