I have been asked to look into monitoring what tables are being used in our DW.
It is a broad question. We have tonne of objects that we need to clean up. I think I will be more interested in tables that are being read than written into because we may be writing into tables in a nightly batch that is not being used.
There is a table called V$segment_statistics that stores this kind of information but only since db was last started. I am thinking I could write this view into table everyday before restarting db so that there is good data to analyze over the period of time.
Now the "flaw" in this request:
How long do you "monitor" before saying with 100% confidence a table isn't used?
What about a table that might only be used once a year? once every 10 years?