Avatar of gs79
 asked on

Table usage

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.

Are there any other better ways to do it?
Oracle Database

Avatar of undefined
Last Comment

8/22/2022 - Mon
slightwv (䄆 Netminder)

I would probably set up auditing and monitor over 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?

It's for analysis purpose. Any decision we take about dropping/retaining the unused objects will be manual after a thorough review
slightwv (䄆 Netminder)

I understand what you are asking.  I've seen these types of questions several times on the site.  What I'm suggesting is that getting this from the database is problematic because of my questions above.

You may "monitor" for X number of days/weeks/months/years and think that it is good enough.  As soon as you drop a table, it is needed by some process that runs the next day and everything crashes.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

I understand the side effects of such that can even appear days/months after. But we may do this with a plan to phase out (like moving  into it's own schema and let it stay there for a period of time etc) the unused objects

I am wondering if it is more appropriate to come up with a custom way leveraging V$ views than using methods internal to oracle such as auditing?
slightwv (䄆 Netminder)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

It's a great thread. Tom is his usual self there. According to him, there isn't a way to do this, so live with it. For now I am thinking of just reading v$ view over the period of time and see what we come up with and go from there

Thanks for the answers