Re: Question about detecting queries

Hi ExecMan,

ExecMan wrote:
> We have a real poorly put together database. It has over 900 tables
> and over 20 schemas.

... my condolences. Hope you're charging a premium for this work ;)

> I want to start to get rid of everything that is
> not needed and merge the schemas into one.

Sounds like a good plan.

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

You can set up object auditing on all tables in the database (for _all_operations, e.g., SELECT, INSERT, UPDATE, DELETE), see:
http://download-east.oracle.com/docs/cd/B14117_01/network.101/b10773/cfgaudit.htm#1006644 and then run the system for awhile and observe the set of tables affected subsequent to the time you started the system's activities.

Depending (very much) on how exhaustive your "exercise suite" is, you will almost certainly see only a subset of tables that are actually "used." This is still a useful lower bound on the amount of conversion you'll have to do, and also will help prevent your getting rid of a table that's actively being used (which is probably worse than unnecessarily retaining a table that did not have to be).

A more laborious, but probably more thorough approach would be to examine the FROM clauses of all the SQL code used or generated by your app(s). This would have the big advantage that you need not actually run all parts of the system. However, it's prone to "misses" especially if the SQL is dynamically generated.

So you could do a combination of the above.

Just an idea,
JH Received on Mon Jan 22 2007 - 12:24:51 CST