Skip to Main Content
  • Questions

  • checking if all the tables in a schema are being used for something

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Wally.

Asked: August 06, 2009 - 1:59 pm UTC

Last updated: August 07, 2009 - 9:39 am UTC

Version: 10.2.0.3

Viewed 1000+ times

You Asked

Background:

Database: Oracle 10.2.0.3
Platform: Windows 32 and 64 Bit

Tom,

Forgive me if I am asking a dumb question.

Given a schema, I am trying to find out if every table in a schema is being used for something.

When I say something, that "something" is either

1. the application the schema was built for
2. sql queries run against the database
3. objects created in the database like procedures, triggers etc.

Please correct me if I am wrong, but for #3 I think I can use the user_dependencies or all_dependencies view.

Am I right in thinking that the v_$sql views are my only option for #1 and #2. I realize that if the object hasn't been accessed in a long time, then there will be no entry in those views.

Thanks a million.

Wally

and Tom said...

for #3, if you do not use dynamic sql - sure, if a stored procedure does use dynamic sql, the things it accesses are not known until it runs.


There is nothing for #1 and #2. Suppose an application hasn't touched a table in six months.

Is that table still needed by the application?


This question, which I get more often than I should, drives me nuts.

Where is the documentation for this system - this must be in house developed (if it is a 3rd party, you don't care - they own the tables, you cannot do anything with them). Why cannot the developers tell you "we need this".


I know of no way to determine if a table is necessary - v$sql is a point in time snapshot of queries, it is by no means comprehensive or all inclusive. There could be a table that is accessed every day at 2am and you'll never see it - because the sql is aged out by the time you look later.


The only way to know if a table is 'used' is to look at your change logs for your source code.

Rating

  (2 ratings)

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

Comments

Wally, August 06, 2009 - 4:16 pm UTC

Thanks for your response Tom. I was hoping (with fingers crossed :) ) you would have a different response, but like you said the v$sql views are a snapshot in time only.

I wish we had documentation for the system. The developers have changed over time and the ones that are here now don't know the system that well.
Tom Kyte
August 06, 2009 - 4:34 pm UTC

... and the ones that are here now don't know the system that well. ...

perfect - time for an INVENTORY then isn't it.

And an inventory starts by.... documenting what you have, what it does, what it interfaces with, what interfaces to it and so on.


Some idea how to do it

A reader, August 07, 2009 - 12:23 am UTC

May be audit helps to answer the question ?.
Tom Kyte
August 07, 2009 - 9:39 am UTC

so, a table hasn't been touched in 2 months.

is it necessary?


No matter what you say - I will say "no, you are wrong, here is why"


Unless you say "maybe", that I would agree with.


Looking to see if something has been touched in any finite window of time tells you..... that it wasn't touched in that finite window. It doesn't tell you if that table won't be used 5 minutes from now (to close out the books at the end of the fiscal year for example) and that they contained the data from last years end of year processing and if you remove it, you totally break everything (just not today - tomorrow it breaks)

There is one way to know "what is needed in a schema". Only one way.
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library