My Oracle Support Banner

Oracle 9i and Below: Exhaustive Troubleshooting Steps for Oracle Database Hanging Issues (Doc ID 61552.1)

Last updated on JUNE 13, 2023

Applies to:

Oracle Database - Personal Edition - Version to [Release 7.1.4 to 9.2]
Oracle Database - Standard Edition - Version to [Release 7.0 to 9.2]
Oracle Database - Enterprise Edition - Version to [Release 7.0 to 9.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.


NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

This bulletin describes the methods and tools used in diagnosing database hanging issues and performance issue). It primarily refers to methods used in Oracle 9i and below.

For a more concise set of collectables see:

<Document 1594905.1> SRDC - How to Collect Standard Information for a Database Hang
<Document 452358.1> How to Collect Diagnostics for Database Hanging Issues

Generally a hang situation will need to be investigated by Oracle Support, but this article may provide some useful pointers.

Hang issues may be due to tuning problems, design problems or Oracle bugs such as latching issues.
We determine the cause by narrowing down the problem and getting as much information as possible WHILE THE DATABASE IS HANGING.

The following diagnostic steps are discussed.

1. Describe the Problems. Under all situations go through this sections and describe the problem.
2. Look for errors.
3. Do the requested queries.
4. Gather OS level data.
5. Get systemstate and hanganalyze dumps.
7. Generate a core dump and run a debugger (contact support first).
8. If certain process get PROCESSSTATE dumps
9. Check List for diagnostics.

Note: It may not be necessary to shutdown the database to stop the hanging. But if you MUST shutdown before speaking to support, please attempt to get these diagnostics so that we can debug the problem.
Without these diagnostics it may be impossible for us to determine the cause of the problem.


Intended Audience: All Database Administrators

Note: This article was created in the Oracle 8i Timeframe and as such is generally applicable to versions up to and including Oracle 9.2 . Later versions may have more extensive or different diagnostics as outlined in:

<Note:452358.1> How to Collect Diagnostics for Database Hanging Issues


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 1. Describe the problem
 2. Look for Errors
 3. Spool out relevant V$ VIEWS
 4. Gather OS-Level Info
 6. Get BSTAT-ESTAT OR (for 8.1.6 or higher) STATSPACK output
 7. Generate a CORE DUMP And Run a DEBUGGER.
 8. If certain processes get PROCESSSTATE dump.
 9. Check List for diagnostics.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.