I thought one epic moment in gaming that deserves a mention is Resident Evil 4, the moment when Leon first enters the rural village.
You enter the village, people are doing work and stuff and then as soon as someone sees you, you've got the entire village on your tail. Climb up that big tower thingo, they start throwing Molotov cocktails at you. Run inside the house and barricade yourself inside, the safety won't last as a maniac storms through with a chainsaw.
I remember playing this game upon its release here in Australia, March 2005 (when I was seventeen) for the Nintendo Game Cube and this moment was one of the most intense experiences I've ever had in a game. It was a very "Ohshitohshitohshitohshitohshit" moment.