Oracle internals, debugging and undocumented features

sqlplus preliminary connection..

I had a very interesting case today when database hang and I was not able to make connection..

First thing I had in mind was to generate system state dumps.In the past I used debugger but I remember that sqplus has option which makes  database connection “light” meaning it doesn not create session but has limited access to SGA…

It has been arround since 10g…  It’s called preliminary connection.There are two options to :

1)  sqlplus  -prelim / as sysdba

2)  sqlplus /nolog
    set _prelim on
    conn / as sysdba              so I tried…
-bash-3.1$ sqlplus /nolog

SQL*Plus: Release – Production on Thu Sep 25 18:04:28 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL> set _prelim on
SQL> conn / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump hanganalyze 1
Statement processed.
SQL> oradebug dump systemstate 10
Statement processed.
SQL> oradebug tracefile_name

let’s see what we can see using  this preliminary connection…

to be continue….

3 responses to “sqlplus preliminary connection..

  1. Balakrishna March 17, 2009 at 11:57 am


    Waiting for your update on this preliminary connection i.e what we can do exactly on this ??



  2. oraclue March 17, 2009 at 1:32 pm


    Main purpose of preliminary connection is to access database structures ( SGA ) in case when normal connection does not work so you can collect dumps and hunganalyze information which will be used for troubleshooting and determining cause of your hung.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: