Re: find what tables are being used in the database

>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

--
http://www.freelists.org/webpage/oracle-l