I'm more for those "Oh hell no" moments, the ones where you just gotta do your best Will Smith impression to yourself.
1. Psi-Ops, Barret fight. It all starts nicely enough, then he picks up a FRIGGING TANKER TRUCK TO THROW AT YOU.
It's kinda crazy when you first see it.
Even crazier is when you play with the Barret skin and throw the truck RIGHT BACK AT HIM.
2. Grand Theft Auto III, after killing a former boss to prove allegiance (after switching islands). I set up cars in front of his garage, his flank went batshit and killed him. I hopped in the closest car, and started to drive away...only to see a hail of bullets from TWO factions.
What made this worse is that I'm piss poor at directions.
It was an...interesting ride.
3. Futurama, the ending cutscene. It was at this moment that I just realized I'd watched an episode of Futurama that few ever will. Geekgasm of the gods, for sure.
4. Metroid Prime, the Ridley battle. At this point, I had powered up Samus as far as she could to that point. The fight drug on for minutes, as we traded damage...Then, Ridley cracked and died. I had 1 unit of power left, no rockets, and beat Ridley while dodging his attacks for about 15 seconds. Now that was a rush.
5. Super Mario Sunshine, the first bootup. When I bought this, it was during my class break. I drove (half an hour!) home just to play it for a short while, then drove back to classes to finish the day. When I first saw what Mario did, I was shocked and awed...this was the true 3D version of the plumber, and I liked what I saw.
6. Infantry, the first bootup. I found it on Download.com, as a free online game. Up until that point, I had not experienced any online gaming...Well, I was pleased as punch. Gigantic in scope, instantly accessible, and something that lasted me for about a year (until the founding community died, and it went pay to play). Great times.