Tracing SQL to find out what is causing hangs

Guys,

Not being an Oracle DBA myself (but experienced with other RDBMSes), but finding myself left supporting some Oracle systems while they are on holiday I need some help with a problem which has occurred.

Database server is Oracle 8.1.7 on AIX 4.3.3 and users are accessing the database via a VPN.

The application has a number of windows, some which access the database and others which talk to a (non-database) authentication server and a file server. Sometimes the users experience a "hang" in the database access windows, but are still able to use the non-database windows, indicating that there is either a problem with the database connection or there is some sort of locking problem. I'd been told to look for "blocking locks", but there are none of these appearing in Embarcadero DBArtisan. The users can shut down the database windows and start them up again to regain database access.

What I'd like to do is monitor the SQL going through the system in the background to have a complete picture of what is happening up until the point of the hang.

On "the other RDBMS" (OK, I come clean : DB2) I'd set up an event monitor to capture all SQL and write it into tables for analysis.

How would I do something similar on Oracle ?

TIA Phil Nelson    Received on Wed Aug 06 2003 - 10:47:11 CDT