Re: how to find unused table /columns in a database

On Thu, 16 Mar 2006 01:44:34 -0800, gpanda28_at_gmail.com wrote:

> 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.
>
> regards

For tables, the solution is called "auditing". You can audit desired objects. 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.

-- 
http://www.mgogala.com