Re: find what tables are being used in the database
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: Casey.Jordan@xxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 14 Jun 2007 08:06:36 -0700 (PDT)
>What is the easiest way to find out what tables are being used in a 10.2.0.2
>database?
>I am using dba_tab_modifications but this only tracks inserts, updates, or
>deletes.
>I am trying to document a database that has been running for about 10 years
>and has over 1500 tables.
>It has no little integrity constrains and no documentation.
Casey
One approach is to use V$SQL_PLAN to identify objects that have been
referenced. As with all V$ views, you only see what's in the cache right now -
but if you monitor over all representative loads, you should get a pretty good
idea. Statspack can collect the data for you (use level 7 iirc and you may want
to fiddle with the thresholds; watch out for additional snapshot load). This
has the added advantage of collecting indexes referenced by plans as well. Note
that if a query is satisfied from the index, there needn't be a reference to
the table object itself.
Definitely combine this with PL/SQL and view dependency analysis, grep-ing
through source code (or string-ing through executables) for all known table
names, etc. It all sounds crummy but can be easily semi-automated.
HTH
Regards Nigel
Other related posts:
