how to find unused table /columns in a database
gpan...@gmail.com
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
sybr...@yahoo.com
Probably columns containing only NULLS are unused, but you would need
to find them manually.
Oracle assumes databases are designed properly, not hacked together. It
also can't read your mind so it can't tell whether these columns are
really unused.
--
Sybrand Bakker
Senior Oracle DBA
fitzj...@cox.net
If such tables are unused now, who is to say when they will be needed?
Removing any table/column from a packaged application is asking for the
application to fail at some point in time. If you have tables which
are not used, what is the issue? They consume little space
(presumably), do not extend, aren't queried and thererfore should have
little, if any, effect on the application.
The tables in the application were designed and created with purpose.
Simply because they don't meet your immediate needs is no indication
they should be moved.
David Fitzjarrell
Mladen Gogala
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.
gpan...@gmail.com
coz, some columns were created for some specific purpose and now they r
obsolte, and some tables were created for development purpose and
forgotn to
drop after the development work was over.
I want to find out these objects, is there any easy way .
gpan...@gmail.com
reg.
Mladen Gogala
Herod
DO NOT DO IT YOU WILL GET FIRED, TERMINATED, SACKED, CANNED, REMOVED
FROM EMPLOYMENT,
You will be become the "You won't BELIEVE what the guy you replaced
did" comment for everybody that is interviewed for your job.
You will appear on http://oracle-wtf.blogspot.com/
You will become the laughing stock of all of the people in your
company. You will be forced to leave the country and move someplace
else where your family will not be happy and you will not be able to
practice your religion in peace and tranquility like you should be able
to do.
Leave the table,columns,data there.
If it is a developer leaving stuff behind, go and find the
developer(s), light a fire under its butt and tell it to remove the
extra stuff from your database.
Then when THEY remove something in production, THEY get fired, YOU get
to restore it and YOU are the hero instead of the zero.
:)
steved...@gmail.com
public static void main(String args[]) {
try {
deleteUnusedColumns();
while (true) {
waitForPinkSlip();
}
}
catch(Exception e) {
System.out.println("No soup for you");
}
}
}
Sybrand Bakker
<gpan...@gmail.com> wrote:
>is there ne way to find these null columns dynamically.
>
>reg.
Yes there is.
--
Sybrand Bakker, Senior Oracle DBA
Marc Blum
> 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.
One question: why? do they do any harm? are they occupying TB of disk space?
make they your backup running much longer? is your life such boring?
--
Marc Blum
mailto:blumXXX...@marcblum.de
http://www.marcblum.de
gpan...@gmail.com
glumtail
For each audit operation, Oracle will automatic create a record with
contains the date and time of the operation, so, if you audit 'SELECT'
operation on every table and analyse the audit records after 1 months,
you may get some issues about witch table could be safely dropped.
Best regards,
Zeeno
William Robertson
> DO NOT DO IT YOU WILL GET FIRED, TERMINATED, SACKED, CANNED, REMOVED
> FROM EMPLOYMENT,
>
> You will be become the "You won't BELIEVE what the guy you replaced
> did" comment for everybody that is interviewed for your job.
>
> You will appear on http://oracle-wtf.blogspot.com/
If you rename a table that doesn't appear to be in use (using a test
database) modules that use it will give runtime errors, which you could
then investigate further. Perhaps you could automate a test compilation
of all modules and see what errors appear. Then it's pretty easy to
rename the table back to what it was if it turns out you do need it.
I'm not a Forms 6i expert, but aren't there any dependecy checker tools
out there for Forms? (A quick Google search found OraDep from
http://www.samtrest.com, though no links about anyone who has tried
it.) Could you at least automate a text search of the source code? That
won't be too reliable but it's a start.