In these cases the SYSDBA (and SYSOPER) privlege holders will still be able to log on and create their session. This is because SYSDBA connections do not generated any audit records to the database tables (that's why there's the audit_file_dest directory where SYSDBA audit files will be written) and SYSDBA connections also don't get their AUDSID from a sequence but use a hardcoded value (0xFFFFFFFF) instead.
But there are cases when even SYSDBA privilege holders can't log on! One situation what I've seen was due a bug, where a process never released a shared pool latch it had taken (in fact it was stuck spinning in free memory searching code). That instance happened to have only one shared pool subpool in use, protected by a single latch.
That meant that no-one else could allocate/free memory from shared pool, causing most sessions to eventually hang.
So, when the DBA tried to log on using SYSDBA privilege, he got a surprise - even SYSDBA logon got hung!
Why? The reason is that also new sessions need to allocate some memory from shared pool, for example for their V$SESSION parameter values (which are session-specific). And allocating shared pool memory would require taking the shared pool latch. But the latch was already taken and held. Thus even SYSDBA session got hung during session creation.
Luckily since Oracle 10g, there is a way to skip the session creation part (which could block) when logging on as SYSDBA.
When you log on normally (even as SYSDBA), this is what happens:
The -prelim option stands for "preliminary connection". What it means is that sqlplus will only complete 2 of the above 3 steps of connection establishment:
Anyway, we managed to log on using the preliminary connection, let's now see what we can query:
Oops! We can't query regular tables nor even V$ views, because we aren't really logged on! Because of the prelim option we do not have all the structures for query execution set up.
However, we can run any ORADEBUG command and the first one I would usually run in such hang cases is HANGANALYZE at detail level 3:
Now there's a hanganalyze trace in UDUMP generated by my preliminary connection's process.
For other commands, like ORADEBUG DUMP, you need to connect to a process (your own for example), hanganalyze works without explicitly connecting to any process:
I will have separate pages for interpreting hanganalyze and systemstate dump output.
Note that even though this -prelim option appeared in sqlplus in Oracle 10g, you can still take an Oracle 10g+ sqlplus and connect with it to an Oracle 9.2 database, as this option is a sqlplus client side feature rather than server feature.
You can just run sqlplus -prelim "sys/password@dbname as sysdba" to connect to a remote database.