There are so many, but these are my favourites.
Ganon, in monster form, getting stabbed in the face by Link in
Ocarina of Time.
Throwing Bowser into bombs in
Mario 64. It was just so over the top that you couldn't call it anything other than awesome. Especially when you manage to do it three times in the final fight.
"For England, James?"
"No, for me."
-
GoldenEye 64
Going home in the helicopter in
Resident Evil. The relief of finally escaping the danger is so great, especially on the harder difficulties, that finishing it will almost certainly make you put the controller down.
Dante defeating Vergil in
Devil May Cry 3, and the very sad ending.
Destroying the Death Star in
Rogue Squadron: Rogue Leader. That explosion was better than the one in the movies. Much, much better.
My first sight of the Netherworld in
Folklore was also phenomenal. I literally just let the character stand there and watched the Faerie Realm for a while, fully rendered in perfect motion.
Namewithheld said:
Bioshock is, in its entirety, an epic send up to the entire philosophy of Objectavism. It exists, and will always exist, as an eternal raised middle finger to that hate filled, homophobic, raving lunatic Ayn Rand.
And for that, I salute it!
You have my sword.