If you want a holy shit moment, do this:

1) Play Eternal Darkness.
2) In the mansion, play until you make it to the second floor.
3) Find a room that looks like a bedroom.
4) Find a door that leads to a bathroom.
5) Walk up to the bathtub.
6) Press the 'A' button to examine the bathtub.
7) Scream, "Holy Shit! What the fuck just happened!"

(Unfortunately, you will be expecting something to happen. I was not, so it felt like my heart dropped into my stomach.)

Spoiler: The screen flashes and then you see your character in the bathtub, wrists slashed open, and the tub is completly filled with blood.  First big scare in the game.  The screen returns to normal just as fast as it happened.  Leaves you with a feeling of "What the fuck just happened".

EDIT: There were a lot of those types of moments in this game.

Resident Evil 4 is pretty crazy, too.

End of resistance 2

Lol some parts in Doom 3.

Definitely. The mirror in the bathroom for sure.

Too many to count.

Arcanum has several of the best holy shit moments.

For example, for the entire game your told your the reincarnation of a supremely powerful elf named nasrudin. The game builds you up as this religious messiah and then what happens later in the game? You find out that nasrudin is in fact still alive. Meaning the loonies had been harping on about you for nothing.

Another example is the evi villain. For the whole game your told its this specific person and when you finally meet him you find out he isn't even evil. Infact he had nothing to do with any of the evil goings on in the game at all.

Basically as a game it sets you up with the usual rpg cliches and then completely shatters them. Its a bloody awesome game.

Definitely the COD4 moment when the nuclear warhead goes off...

The game lets you crawl out, thinking that you might actually survive, and then the radiation finally kills you.

The first time I got attacked by a guy with a chainsaw in Resident Evil 4.

When he got too close to me, at first I thought, "Well, I'm going to take some damage here".

Then he cut off my head. "Jesus Christ! He cut off my head!"

My biggest 'HOLY SHIT!' moment was actually in the original Prince of Persia for PC.

I was kinda young, and not very used to games at all, my previous experience was Super Mario Bros. and that was about it.

So anyway, I was playing all alone in the house, and I finally started to make sense of that first dungeon level. I got the sword and everything. And suddenly that skeleton lying on the ground that I've been running past all these times stands up! And it attacks me with a sword!

I kid you not, I flew up from my chair and turned the computer off, and immedeatly called my mom. Then I was lying under my covers (so to speak), afraid I would get attacked by skeletons, waiting for mom to come home. I was in a state of fear for hours, and my mom had to play the game with me for me to face my fear and get over it.

Yeah, I was like 6 or 7, but I was an impressionable child, with a vivid imagination. No game has ever replicated that fear since, but a few movies have. But that's another story.

I think that killergran has won the thread. I doubt any of us have had a bigger Holy Shit reaction than that.

That's exactly what happened when I watched the movie "IT"............except I never got over clowns.