In Resident Evil 4, there's a huge creature that chases you in a confined space. Despite his huge size, it's very fast and can kill you in one hit if you're not careful. It's a very intense moment when you're trying to escape the room while escaping from the creature. Oh, and if he gets you, he bisects you, it's not a very pretty sight.
I keep re-reading this, and I've played it a bazillion times.. but I just keep thinking, "I do not recall this at all."
My immediate go-to for nasty enemies from RE4 is the Regens/Iron Maidens.. they absolutely freak me out. But they're so slow..
Is it the Garrador? o:
I think Mother 3... You wouldn't think that could happen in a turn-based RPG, but having the opportunity to save a mortally-damaged party member if you act fast enough really gets the blood pumping. XD
Skyrim
Whenever I go to a new dungeon and a Dragon Priest is around, I get really excited at the idea of obtaining their mask. Also when I fight a dragon or other boss which is clearly stronger than me, and then actually defeating them by a the skin on my neck. Very satisfying.
Two words.
Hyrule.
Warriors.