The cause of this condition is often varied, ranging anywhere from problems with the PC, with Windows, the network, the file-server, the database-server, and of course, the database itself. (This is by no means a complete list of causes, rather it is an illustration to show the diversity of reasons.)

As an Oracle DBA, it is my job to determine if the problem is related to the Oracle database. Just by attempting to connect to the database with SQL*Plus for Windows, much can be discovered about the problem. For example, if a connection is made, then we know that part of the network and the database server are working okay. We also know that the database and the SQL*Net listener are up and running. If the connection failed, we would begin researching those items just mentioned (the network, the database server, the listener, and the database) to see if one had problems.

Assuming a connection to the database can be made, the next step would be to run a script called sessions.sql. This script, which I created initially for Oracle 7.1, provides quite a bit of information about the different sessions connected to the Oracle database, including: the username, status, type of session (shared or dedicated), number of open cursors, and time connected. Most important to the situation being researched are three columns: Status, Lck, and Lat. These columns tell us what, if anything, is holding the session up in the database.

If there is an X in the Lck column, you can begin researching application locking issues. If there is an X in the Lat column, you can begin researching contention for system resources. If the column Status is ACTIVE (while Lck and Lat are blank), then the session is busy doing work, and you can look into performance issues. Finally, if the column Status is INACTIVE, then session is not doing any work, and the problem is probably outside of the database.