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.
Problem solved I think.
Our thanks to Herod T for the plug.
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 :-)
Thanks for the link.. I don't think that poor fellow is going to take the hint. Oh well, I hope I see his resume come across my desk so I can talk to him in person.
He just needs a mentor.
> 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.
Post a Comment