For multiplayer- Star Wars Battlefront 2. I haven't put nearly as much time into any other game online, and its paid off as far as those moments go. Clearing a room of real players is a blast.

Singleplayer-

Commander Keen 4-6 and various fan mods. The later Keen games really let you do a lot of tricks (mostly involving your pogo stick) that are hard to pull off, but are pretty amazing when you manage it.

Return of the King for hack-n-slash. Getting on a streak and killing 4 orcs with one blow from Anduril is pretty awesome. Or turning on Gandalf's shield.

Second any Jedi Knight game.

There's some games that would be cool but are hard to pull off in a "rage mode". Allied Assault comes to mind. If you run out in the open on hard, the AI are going to hit you.

First time I played doom 2 with the brutal doom mod. It's quite an awesome experience and after finishing the game more then I can count (i only have 10 fingers) this also felt like a new experience. The makers did an amazing job tbh.

Whenever I'm running around in Undead Burg and Undead Parish in Dark Souls with my Lvl ~60 character (I know, still a noob though), sporting a lightning greataxe, oneshotting enemies and only taking a fraction of damage if they manage to get a hit through.

That feeling of invincibility quickly fades away when I go to a new area, and especially in boss encounters. Then I become the one who is getting oneshotted.

Of course!

Playing as the Heavy in TF2 =D

Everytime I play Dynasty Warriors, though that's normal there.

It happened to me in Dark Souls, when I learned to parry somewhat decently.