I love being scared, so I've really had a lot of moments like these because I get so engrossed in the moment that it takes hold of me.
Doom3 is really the game that redefined being scared for me. I was scared for such extended periods of time during that game that it actually manifested into my play style, how I walked, how I explored, how I fought. The Cherubs were a real "oh shit" moment for me. I had some serious trouble fighting them in game because I'd sooner fire a rocket into the floor or wall then I'd let one get any closer than 30 feet from me. There is a moment early on in Doom3 where an imp bursts through a staircase right in front of you. That moment, more than any other, made me jump so hard that I nearly knocked my keyboard off my desk. It was early in the game and I was definitely not prepared for it.
2 Classics:
Eternal Darkness bathtub scene
Resident Evil 1 (or 2?) one-way mirror
As far as System Shock 2 goes, I remember that the first time I tried to play it (near when it originally came out), I stopped playing directly after my first oh shit moment in one of the hangars early in the game. It scared the hell out of me. I actually played and beat SS2 in it's entirety after beating Doom3 and having any semblance of fear beaten out of my body. My new perspective made SS2 a lot more enjoyable, although midwives were still freaky as hell.
Also, I agree with most of the others mentioned: Undying, AvP series (I never got further than level 2 in AvP, any campaign), STALKER, etc.