Re: Question about detecting queries

On Jan 22, 9:55 am, "ExecMan" <john..._at_mtekusa.com> wrote:
> Hi,
>
> We have a real poorly put together database. It has over 900 tables
> and over 20 schemas. I want to start to get rid of everything that is
> not needed and merge the schemas into one.
>
> One thing I need to know is which tables are used and which are not.
>
> Is there a way to detect when a query is done to a table? This will
> tell me if the table is used or not........

All the other replies and your basic assumption have one fault... what if the table is infrequently used? Historical tables and year-end are two common examples.

You need to check all source code.

Depending on whether your system is not entirely custom-built, there may
also be upgrade issues. For example, I've seen COTS systems that separate out modules by schema, and then are customized by layering other schemata on that. While it sounds icky to some who think "just modify the schema!", I've seen it make a big difference on the next upgrade(s).
On the other hand, if you've got a gazillion synonyms to make it work, that
might have some performance implications. On the other other hand, often these many-schema designs come from database-independence, and it can be a good thing to Oracle-ize them. Kinda depends if you've got dozens of programmers available, too.

jg

-- 
@home.com is bogus.
http://www.chinesejetpilot.com/index.php?ID=202