I really get into games, so this may not seem like much, but was sure as hell an oh-shit moment for me.
I was doing the O Cristo Redentor Spec Op mission in MW2, on Veteran, by myself.
I'd been trying this for days, and I had finally gotten it down to 1 out of 50 guys left to kill. So I go out to search for the guy. I round a corner, and that's when I discover that the game spawns more than just 50 guys. I was immediately barraged by four trigger-happy members of the militia.
And then there was the time in Borderlands.
I was in the fight with Mothrakk, which wasn't so much hard as it was tedious. I finally kill the giant bastard, and I start picking up the metric shit-ton of loot that he drops. So I'm picking up money, when I notice a whole bunch of little red dots on my compass, that were steadily getting bigger. I turn towards them and see a veritable ARMY of Rakks flying towards me. Sure they weren't hard to kill, but it was still an OH SHIT moment.