Two moments come to mind:
The first time I saw Mario fly in Mario 64 - I've played all the Marios and although he flew in Super Mario World, seeing him fly in glorious 3D just about brought a tear in my eye. I've seen Mario evolve through the years and when I started collecting the coins in that aerial spiral stage it just felt like Mario - my boy - grew up. Sappy but true.
Doom2 and the Baron of Hell - 4:30AM: the labyrinth (forget the level) where I was completely lost, depleted of most of my health, and with no more medikits, I went searching for the exit and stumbled upon a door which I opened - and there he was. I ran away, but everywhere I went could hear his footsteps and growl as he hunted me down. Since I had no idea where to go, it was pitch black and he was just downright frightening, I was scared $hitless to the point where I simply hit the power button on my PC. No exiting the game, no reloading, just turned the damn thing off. That was the only time I was ever so scared in a game in my life.
I'm sure there are many more moments, but these just popped into my mind.
