Monday, March 20, 2006

A poster on the hardcore comp.databases.oracle.server newsgroup had some tables in the production database that he felt were redundant, and asked, not unreasonably I felt, whether there was a way to tell from his Developer 6i application whether they were in fact in use:

hello

we are using oracle 9i production database and d2k 6i applications.
is there any way by which i can find the tables/ columns that are not
in used by applications. So that i can move them out from our
production database.

can anyone throw some light how to do it.

After the obligatory initial responses along the lines of "it can't be done" and "you are an idiot for even thinking about it" (you take your life in your hands when you post on cdos), one respondent begins,

For tables, the solution is called "auditing". You can audit desired
objects.

Reasonable enough. Give it a year, then if nobody has used the tables, they are probably not so important. Then he continues:

For columns, you should be using so called decibel method:
if you suspect that column C1 in table TAB is not used, you can always
execute the following commands:

update TAB set C1=NULL;
commit;

If the reaction to that is a loud scream, accompanied by swearwords and
a genuine cornucopia of various expletives, you've made a mistake, the
column was used. It's time for the "I'm sorry, I didn't know that this
column was still being used" routine. You can rest assured that this
swearing sucker is gonna be busy for a while.

If not, you can proceed and drop the column. The previous update has an
added benefit of making "drop column" operation faster. It will also
expose weak points in all those lousy applications that use "select *" and
expect the table to populate all of their variables.

An alternative to the decibel method is fine-grain auditing, described in
the books by D. Knox. It's much more tedious and requires much larger
knowledge then the decibel method, which is also a lot of fun.

4 comments:

Hey, this decibel technique is awesome. For network people, instead of paintstakingly following each wire to see if its connected to something, just yank the cord out and listen for yelling. If someone yells at some point, review all the cords you pulled out in the last year and figure out which one they're yelling about :-)

> instead of paintstakingly following each wire to see if its connected to something, just yank the cord out and listen for yelling.

At least there would be a wire to push back in. With the "set the column to NULL" approach, if that data turned out to be important (and the guy yelling is your boss or Finance Director, or a customer) you're going to need a backup and a spare afternoon.

Unless, that is you take Rob Watkins advice and "unlearn" that we need a DBA to recover lost production data:

http://techrepublic.com.com/5100-10878_11-5193687.html

I wonder how he's got on in the intervening 2 years after allowing his users to recover "accidentally deleted" employee records from the production system by allowing them to INSERT INTO emp SELECT * FROM emp AS OF...after having aparently issued FLASHBACK ANY TABLE to the user community.