Tanel Poder gave a great answer. Now it's nearly clear, at least for me:
-prelim allows sysdba to start a preliminary connection to db, which
means that a server process is started for you and it attaches to sga
segments, but it does not allocate anything inside sga (no process
state nor session state object and session structures are allocated
for example). This means tha no latch gets are needed in prelim mode
and if you have an instance hang due these latches your session wont
get stuck. You can do very limited things with prelim mode, mostly
just various dumps with oradebug.
Oradebug direct-access allows you to also query few x dollar tables
with sql-like syntax. thats fake sql though its oradebugs hack, not
real sql. The same is doable with oradebug dumptype as well.
Or, to summarise it (just for me):
- -prelim allows me to start a sqlplus binary and connect it to the (correct ≈ right $ORACLE_HOME and $ORACLE_SID) SGA without creating anything within this SGA. So for me it's the cheapest way to get access to the shared memory of the specific instance.
- this prelim state is of no really use in ordinary life. Just for some really rare occasions, it might be of any use. Most of these are oradebug variants. I have not tested any ALTER SYSTEM or ALTER SESSION statement yet. Some of them might be worth testing, but most will fail. Ordinary SQL will fail all the time (I'm sure).
- oradebug direct-access is a nice gadget to query some x$ tables without really going through all the sql-parse-pin-heap-whatever engine ;-)
- at last, -prelim is a kind of last resort to access an instance and at least get some informations, until some really hard direct SGA access methods must be considered