Oh heck, this is going to take a while,
WARNING!! SPOILERS AHEAD!!
# Mass Effect 2; On my first playthrough, with no cheats, restarts or references to the wiki, I brought my whole team through the suicide mission. I whooped like a lunatic for a good five minutes during and after the final cutscene.
# Brutal Legend; Two parts for this; First, when Lars Halford faces down General Lionwhyte in the Pleasure Gardens, and as "Queen Of The Masquerade" by Crimson Glory kicks in, and Eddie roars his battle cry, it makes you feel as though you could take on the world. Secondly, the Death of Emporer Doviculus, summed up in one word; DECAPITATION!!!!!!!!!!!!!
# The final battle in Viking: Battle for Asgard. Leading the charge of an army of vikings against legions of damned souls while dragons circle above ticks all of my myth fantasy boxes.
# God of War III and the Death of Hades. After carving chunks out of him, stabbing im in the back of the head, braining him on the ceiling, cracking his skull open and stealing his soul, the satisfaction of seeing him finally get dragged down by swarms of souls into the River Styx is enormous.
There are others too which just happened in-game as a matter of timing and luck. Two that spring to mind immeadiately are during the destruction of the SSV Purgatory, when about to be confronted at point blank range by a heavy mech, Zaeed Massani (who I'd stationed up on the bridge) pulled off a cracking shot, obliterating the head. Then a timer started counting down, I couldn't have got to better cover in time, and so the mech detonated like a bomb with me sitting just the other side of it, completely unharmed by the explosion, that was a hell of a pulse raiser.
The other (last one I promise) was during Grunts' Loyalty mission, when confronted by a group of Gatatog Krogan at the end of the Rite. All the other Krogan were dead, but my shields were out, I needed to reload, and Gatatog Uvenk (the green-plated Krogan arguing with Wrex and the Shaman back at the Urdnot camp) was charging at me. Just as he was about to trample me, BOOM! He dropped like a stone, I turned, and saw Grunt standing there, his Claymore shotgun still smoking. That was a "F**K YEAH!!" moment if ever there was one.