DanTheBigMan:

Ice Storm:
The very first flood level in Halo: Combat Evolved. Oh my, I was scared as hell.

Not to mention that's it's almost bloody impossible to complete. If I had a nickel for every time I died in that level, I'd have enough cash to buy fifty more copies of the game.

Indeed, it's so hard to keep your cool in that level to find the way out without the flood killing you for being too slow.

Freespace 2
"Dive dive dive! Hit your afterburners pilot!"
Jeez that was one of those moments when I had realized that I had in fact turned TOWARDS the insanely large ship. With my afterburners. *sigh*

Fallout 3
So, there I was, happily shooting away at a group of Enclave.
I saw one running towards some large-ish metal cage, but ignored him and focused on the Officer who was trying to flank me.
How wrong I was.
*gets torn to shreds by a rather angry Deathclaw*

Mass Effect
Running into a room absent-mindedly, riffling through one of the containers there, and ouch. Geth Armature.
*Dies horribly*

MsDevin92:
In Guild Wars: Nightfall, there's a quest where you escort this librarian into a hidden city to find 'the Gift of the Djinn' or something like that. You have to beat two bosses (I got stuck on the second one, as he was in the middle of a four-way intersection surrounded by fire-nukers and these REALLY annoying lion things that can resurrect each other). After I beat the second boss and put the essences or whatever on the pedestal...

...a huge mob of Djinn shows up and starts chasing your party to the exit.

Both an OH $#!+ moment and an AW MAN moment, because elementalists are not fast and I got pretty much massacred...

Oh, and any time in the Fissure of Woe when that idiotic Rastigan the Eternal plunges into the mob on the OPPOSITE SIDE OF THE FIELD of the mob I had been clearing for him, and gets killed.
...HOW DOES AN ETERNAL THING GET KILLED?

There are quite a few "Oh $#!+" moments in FoW and UW in Guild Wars I must admit. Realizing that 1 of the NPCs you CAN'T let die has just ran off into a huge mob is....worrying to say the least.

Possibly the end of the first section in Doom 1 (after you kill the two dragon like things) I was all like Oh fuck!!!! tried to save my self but died and found out it was a non wolfenstein ending

other ones would be trying to sneak around in Oblivion when there were strong enemys and i would just happen to get in the sight of one

Cod4: Mile High Club: got around the last corner before you save the civilian and a dude pops out of nowhere with a shotgun, blasts me in the face and kills me, strangely he was the 3rd enemy around the corner when there was usually 2... and I wanted the acheivement

In Left 4 Dead we were in the helicopter in the No Mercy Campaign while we were waiting for the other half of our team to get on. Unexpectedly a tank shows his face and whacks a teammate that was about to jump into the helicopter, off the roof. We were like "OH $#@2! GET IN THE F%@ING HELICOPTER BRANDON!" Then the tank killed him off to. Another one was when we were playing Blood Harvest and we went into the fields. Our friend, Justice, started to sing "I want to hold your hand" then got mauled by a hunter while I got dragged by a smoker while the horde came. It was Brandon that said OH S%@T! while me and Justice ended up singing the lyrics to "I Am The Walrus"

EverQuest - most often when a mage or enchanter starts throwing his whammy on the pull before it's in range of me to tank it, let alone to have built up any aggro.

Overall, I only get those moments when my wife is in the room (playing along on her computer). Otherwise, it's just a game. But when the shared experience is alive and in the room, the adrenalin pumps a little and we're in it together, and then ... Oh $&*!. Also because she WILL take it personally if a pull or quest goes south, and then I'm the one who has to bring her back to cheerful. You've never fought a boss battle like that. =P

Mine is crash bandicoot.

You know those levels where you run from giant rocks?

That scared the crap out of me the first time, i was eight.

Doggabone:
Overall, I only get those moments when my wife is in the room (playing along on her computer). Otherwise, it's just a game. But when the shared experience is alive and in the room, the adrenalin pumps a little and we're in it together, and then ... Oh $&*!. Also because she WILL take it personally if a pull or quest goes south, and then I'm the one who has to bring her back to cheerful. You've never fought a boss battle like that. =P

Oh, I can relate a little bit...
I've been in a few WoW raids over at a friend's house, and it seems to always be my fault when the shit hits the fan.
I think he's just jealous because I can whup him in PvP. >.>;