Some come to mind.
Basically the whole of Spire in Revelation. Just a remarkable experience.
The ending of Monkey Island 2. Just weird... heavy Lynch warning ;-)
Realizing your true identity in Knights of the Old Republic. I'm rarely caught off guard, but I didn't suspect that until right before it was revealed.
Introduction sequence of Wing Commander 3 when Prince Thrakhath refers to Angel as "
... lair mate of the Heart of the Tiger". Having played both Wing Commander 1 & 2 plus all the expansion packs before then, that line sent shivers up my spine :-). Hey, it was cool realizing that your previous exploits had earned you a nick name of that magnitude!
Anachronox when whatsisname answers "You'd be amazed how seldom people look up" to the question of how he knows so much about peoples secret affairs. He says this just before flying off.
There are probably more, but I'll just pick Wing Commander 3's intro. Yes, I know it's not a "true" adventure, but the game is an adventure and that's good enough for me :-P
