What games gave you a "Holy S#/?!!" moment?

After reading an posting on the "what games made you cry" thread i had the idea for this one. 
What games have you played that made your jaw drop either from a twist you didn't see coming or an event that blew a circuit in your brain, it was so crazy.  Try to use spoiler tags whenever applicable if it's recent enough.
 
Two i can name off hand were:
1) Aeris dying. Kinda an obvious answer but at the time my jaw was on the floor.
an more recently
2) The Andrew Ryan scene from the first Bioshock (i'd say that's vague enough..and if you haven't played it, what are you waiting for?) that's one of the very few times in a game that i was playing in a room by myself and verbalized out loud "Holy Shit!!" i don't think i blinked during that entire scene. That and the revelation that followed just knocked me on my ass. 
 
I know there's more but i forget...
Anyone else?

Call of Duty 4's nuke. 
 
I mean, yeah the nuke was in the trailer; we all knew it. But I didn't know I'D get nuked.

The very last jump scare in Dead Space, I freaked the hell out at six in the morning. 
Pretty much the last hour of Deadly Premonition where everything goes absolutely crazy, even more crazy than usual DP...yep, leaving it like that. 
The most memorable for me though is when  Mask De Smith HEADBUTS A GODDAMN BULLET in Killer 7.

" Call of Duty 4's nuke.  I mean, yeah the nuke was in the trailer; we all knew it. But I didn't know I'D get nuked. "

"... the most memorable for me though is when  Mask De Smith HEADBUTS A GODDAMN BULLET in Killer 7. "

That bit in Portal where you realise the cake can go get fucked.
COD4's nuke, fer sure.
Bioshock for obvious reasons.

FF6, when

CoD 4 nuke. 
Rock band 2, basically because I holy shitted because of something I did. 
Heavy Rain, for like everything. 
Dead Space. 
Read Dead Redemption, because of the ending. 
Left 4 Dead 2, finale for Dark Carnival, wasn't amazing but was really cool. 
 
 
Really strange ones from me, but yea, probably alot more out there.

" @blazerx9x:  I don't think i gave anything away. Aside from Aeris dying. That's pretty much public knowledge. "

" @audiosnag said:

" @blazerx9x:  I don't think i gave anything away. Aside from Aeris dying. That's pretty much public knowledge. "

True, sorry, i`ve been spoiled of the Aeris death thing before, severely crushed one of the biggest plot twist`s for me, but uh, meh, yeah everyone pretty much knows these days. "

 
1) Ocarina of Time's field. A large vast 3D environment, brought to life by actual time changing. Anyone who was playing games during that time period, who first experienced that knows what I'm talking about.  
2) A bunch of people like to point out Aerith's death in FF7 as the big "Holy Shit!" moment. I agree it should be in there, but for me personally, nothing really comes close to the first twenty or so minutes of that game. Watching the opening skyline shot's of Midgar, and then transitioning immediately to gameplay was something that made me proud to have a Playstation.
3) Speaking of Final Fantasy: Final Fantasy Freaking VIII. Beach sequence that was like Omaha Beach, being chased my a giant robot crab, the assassination sequence, and least but not least, The Battle of the Gardens. For a freaking "love story", this one seems to have a bunch of explosions. 
4) Marathon. I'm on a ship. There's something moving over there. It's dark. Oh fu-
5) Marathon 2. Sweet I found the control panel for the base's "lava control flow". Whatever the hell that means. Better smash it and.... Oh fu-!!!! RUN! RUN!!! Durandal you prick!
6) Halo and the flood. Or Halo and the...."WTF IS THAT?! DO I SHOOT THAT?! SHOOT! SHOOT! SHOOT!" 
7) Bioshock's Rapture reveal.
8) I just spent like, eight freaking hours in GTA 3 and have accomplished nothing. Except causing lot's....and lot's of property damage....
9) *Walks into church in Dragon Quest VIII* *Has volume turned on* *Fully Orchestrated Symphonic Soundtrack kicks in* *Vidiot's jaw drops to the floor*
10) The realization that a rail shooter is perfect for an RPG.

Alan Wake 
 

The lincoln park scene from Mafia 2.

Secret mission in Starcraft 1, with Samir Duran

One of the most memorable for me in the last few years was when the Enclave finally show up in Fallout 3.  I did the typical Bethesda puttering about for a dozen hours before taking up the main quest, and spent a pretty hefty chunk of that with the Enclave Radio on in the background.  I had never played a Fallout title before, so my only exposure of the Enclave was through Malcolm MacDowell's over the top folksiness and discordant naivete.  I thought the Enclave were a pretty funny joke, reducing a superpower to a dottering self-identified "President" promising Little League as a panacea to the apocalypse.   
  

All of Uncharted 2
 
Red Dead Redemption

For me it was the original mass effect as the reapers were uncovered.

The first colossus you see in Shadow of the Colossus and some more of the colossi in the game have the same effect. Holding on to the wings of that flying colossus was awesome. I can't wait for the ps3 package of ico/colossus.

" The lincoln park scene from Mafia 2. "

Just this year: 
Finale of Mass Effect 2 
 
Ending of Red Dead Redemption 
 
All of Heavy Rain

 
Mass Effect 2

Throughtout Dead Space, giving me a hell of a fright every fucking corner

I can think of a lot, really. For starters I'm gonna go with ...
 
Silent Hill: The Room.
 Let's just say, I'm talking about two(!) rooms you'll enter later in the game, not the flat you're actually trapped in.
One, you'll just waltz in there not expecting anything out of the (creepy horror-)ordinary and BAM! You find yourself looking at a ... a ...
The other one you expect to be creepy--whole in the wall, disgusting smell and all--and the game delivers.

when you meet Andrew Ryan in bioshock

One of mine was in The Darkness when Jackie is forced to watch his girlfriend get murdered right in front of him. 

I had a couple of these moments in a row while playing Red Dead Redemption.

1st Colossi 
Nuke
Samus reveal

Homeworld: All of it. But honestly? The first two missions are the ones that really got me. The first one is a fairly straightforward tutorial but the sheer scale and amazing design, plus the undocking of the mothership just blew me away, then coming back to the planet and what happens there combines to make my favourite gaming moment of all time. 
 
Freelancer: An ok space sim with decent sense of style and great online but flying into the Dyson sphere in the campaign was just effin' rad. 
 
Mass Effect 2: The beginning. I have to put this slightly in context since it was the first game I played on my new computer. I went from a GeForce Go 7300, 2 gigs of ram and some crappy cpu to an up to date gaming machine. Without hyperbole, it was a bit of a revelation. 
 
I could probably remember a lot more if I put my mind to it but these are probably my top three.

Mass Effect 1 and 2, both have amazing moments and some of the ending parts made me go OH WHAT DA FUCK.
 Dragon Age's ending was pretty awesome too, same for the DLC Awakening's ending.
 
And well, Monster Hunter Tri.
This guy isn't kidding!

Most recently Split/Second. Obvious reasons.

Off-hand, the most memorable one for me is Arkham Asylum's Scarecrow sequence: I totally did not expect it. :) 

" Throughout Dead Space, giving me a hell of a fright every fucking corner "

" Off-hand, the most memorable one for me is Arkham Asylum's Scarecrow sequence: I totally did not expect it. :)  "

I was playing MotorStorm once... To anyone familiar with the game, it was on Dust Devil which has a valley halfway through the level. I was on a bike and there was a big rig trailing my ass, Terminator 2 style. I look behind and just as it's about the ram me it hits a rock in the ground and explodes in midair.
 
Holy Shit.

In AC2 while climbing up the tallest tower in... umm... Venice? It's the tallest one in the entire game which you need the monkey spring type jump to get up. Anyway, I genuinely got a feeling of vertigo on the way up, and it took so damn long to get up the fucking thing! I was relieved when I reached the eagle viewpoint because I had screwedup a couple of times before by accidentally doing the wall run n' jump thing. And then that glorious dive to the bottom.

KotOR1 finding out who Revan was. 
  
Bioshock realizing that that intro was in engine and that I'm actually in control after the crash. I kept waiting for him to swim a few seconds before that oh snap moment when I understood that this was the game and not a pre-rendered cut scene.

The part in SS:FE where you have to run from Ugh-Zan III while dodging enemies, and the Grand Cathedral in SS:SE were both pretty awe-inspiring.
 
Also, the bathtub scene in Eternal Darkness. $#%@ caught me unaware.
 

If you mean a HOLY #$%^ surprise then I'd have to say AC2 ending, Heavy Rain ending and the post-credits monologue from Gears of War 2 for recent ones. If you mean a moment when my jaw just dropped then I would say the first minutes of the Xbox Ninja Gaiden, walking out of the train in Half Life 2, the opening sequence of Oblivion when you see the castle from the bird's eye view, and most recently God of War 3 when you are fighting Posidien on the back of Gaia and all of Uncharted 2.

Yeah, Half-Life 2 have a lot of jaw dropping moments.

Killing Chronos in GoW3 ;)

Silent Hill 2, the reveal of Pyramid Head. Brrrr.

" The very last jump scare in Dead Space, I freaked the hell out at six in the morning. Pretty much the last hour of Deadly Premonition where everything goes absolutely crazy, even more crazy than usual DP...yep, leaving it like that. The most memorable for me though is when  Mask De Smith HEADBUTS A GODDAMN BULLET in Killer 7. "

" Alan Wake 
 

SPOILER WARNING: Click here to reveal hidden content.

It was all a dream!  BUT WAIT! Look out the window to see the darkness swirling outside and realize that the darkness has you more fucked than you realize. 

 
The Darkness 
 

SPOILER WARNING: Click here to reveal hidden content.

The last ten minutes.

"

Mass Effect 2:

Pretty much anything that happened in Bayonetta . 
 
The first time I saw a regenerator in Resident Evil 4. That "Oh, Shit" turned into a terrified scream for me. 
 
The secret video in Assassin's Creed II.
 
The Joker scene in Arkham Asylum. If you played the game I'm sure you know what I'm talking about.

The twist in Knights of the Old Republic, the flood first showing up in Halo, and the ending to Halo Reach.

COD 4 Nuke
 
Doom 3 - No flashlight + gun combo resulted in numerous "Holy S%/#" moments.
 
MGS4 - The last "crawling" sequence.