Wally -- Thanks for the question regarding "checking if all the tables in a schema are being used for something", version 10.2.0.3
Submitted on 6-Aug-2009 13:59 Central time zone
Last updated 7-Aug-2009 9:39
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 we 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.
August 6, 2009 - 4pm Central time zone
Reviewer: Wally
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. Followup August 6, 2009 - 4pm Central time zone:
... 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
August 7, 2009 - 12am Central time zone
Reviewer: A reader from Russia
May be audit helps to answer the question ?. Followup August 7, 2009 - 9am Central time zone:
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.