sqlplus -prelim option

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

using sqlplus -prelim we can connect to sga but not to database…means no session is created

This option is very much helpful in hanging situtation where we cant connect to database but we may need to execute commands like oradebug.

you have any Sql*Plus 10g or latter version (ex. from client installation) and a valid net service name to connect to your 9i database you should be able to establish a “backdoor” connection.

just connect to the database using SYSDBA account

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

for example

C:\>sqlplus -prelim sys/oracle@mytest as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 9月 26 11:53:25 2011

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

SQL> oradebug setmypid
已处理的语句
SQL> oradebug unlimit
已处理的语句
SQL> oradebug dump hanganalyze 1
已处理的语句
SQL> oradebug dump systemstate 10
已处理的语句
SQL> oradebug tracefile_name
/u01/app/oracle/admin/orcl/udump/orcl_ora_3958.trc