Jonathan Lewis pointed to an old blog from Arup Nanda, where he describes how he used sqlplus undocumented option (-prelim) to connect to “hanged” Oracle instance. Since I was not aware of this command I decided to try this out with 10g on Windows.

I tried this on Windows 2003:

C:\ORACLE>sqlplus -prelim /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Tor Avg 11 13:48:35 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in C:\oracle\admin\oraT\udump\oraT_ora_3336.trc
SQL>

and sure enough it can also be done in another way, described by Virag Sharma:

C:\ORACLE>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Tor Avg 11 13:40:35 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 12
Hang Analysis in c:\oracle\admin\oraT\udump\oraT_ora_3812.trc
SQL>

Miladin Modrakovic also blogged about sqlplus -prelim option.

I can’t even remember when was the last time I have to troubleshoot (seemingly) hanged Oracle instance, but it’s good to know that Oracle gave us an option in 10g to knock on the back door and ask for a dump of SGA structures.