In regards to horror games I'm admittedly quite a wimp. So far I have yet to try the likes of Resident Evil, or most of the names mentioned in this topic for that matter. xD
One moment that definitely scared the shit out of me was in Super Metroid for SNES though. When I played it for the first time a few years ago I already had played the GBA Metroid titles and was familiar with the environment of the series. Super Metroid, however, managed to keep me on my toes many times throughout, especially from Lower Norfair onwards.
To be even more specific, that final area where
you finally encounter the Metroid larva that was stolen from the space station, now massively oversized and immune to all your attacks, sucking you down mercilessly to 1HP health until it finally realizes something, and later
the 2nd boss phase of Mother Brain, with it's giant SHOOP DA WHOOP... I mean Hyper Beam lockdown of doom. On both situations I felt so overwhelmed I thought I missed some key item or skill beforehand, until I realize there's nothing I could do but watch (or in the latter situation, pray and fight back holding my heart tight).
On top of that, Super Metroid is a game without a dense narrative. All storytelling was done through small animated sequences alone. This made the end-game experience really, REALLY memorable, IMO.