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

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. >.>;