Might & Magic - World of Xeen
The last level of the Dungeon of Death.
Silent Hill
The motel.
Metroid Prime 2 - Echoes
Pretty much every second.
Half Life 2
Mentioned by many, the big thing for me was toilets. No gameplay use, but every apartment/building had one. In my opinion that's truly immersive. Toilets.
Tekken 3
A round I had with a buddy of mine that basically consisted of blocked close quarter attacks and seemed to last for five to ten minutes. Felt like a scripted action scene.
Fallout 2
The game just has a strange, beautiful charme in every aspect of it. Not any particular moment and no big WOWs, just tons of small "well, this is neat"s.
Chrono Cross
Every time a new piece of the soundtrack played.
Legend of Zelda - Ocarina of Time
Pretty much any Nintendo game I play now, but especially this one has me going "WOW, I can't believe the stuff these guys get away with".
Parasite Eve II
Facing sick crap the entire game, starting in what's essentially a ghost town in the middle of nowhere, leading to a high-tech underground bunker, leading to a wide open prehistoric area below that, just to find a nursery below that one. And then the toy phone rang ...
Biggest gaming WOW!! so far by a landslide.

Space Hulk
Those moments when you're out of freeze time and staring tensely at all 5 displays for your terminators and half of them contain oncoming genestealers and your storm bolter jams

Ooh the Chaos Gate music is awesome, it'll be a great bonus when GOG get the game... Which will be when?

System Shock 2..as SHODAN lets me know I've been a pawn...
Police Quest 4, in the autopsy room : "What the HELL have I gotten myself into?"
Doom3 in combat: emptying every last cartridge in a running battle with a horde of monsters..."AMMO.....gimme more AMMO, dammit...."
Starcraft Brood War as I finish the singleplayer storyline for the first time..and sit there, shattered and emotional as I realise that this game was'nt about action, or strategy...but about the sheer terror and waste that is war....
Riven and Uru, walking the streets and revelling in the sheer beauty of my surrounds...
and most of all...
...The Dig, a glorious adventure ending in a note of hope and wonder, as Mankind faces a new and exciting beginning.

You can tell I'm playing Fallout 3 at the moment but I've just found another one. In Vault 106 you have hallucinations and I accessed a computer which had a "Note From Me" on it. Its not QUITE up to the level of the malkavian and the stop sign but its in the same area
Hahaha I just had another one. 106 is a creepy arse place that gets close to triggering my claustrophobia so I'm a bit nervy in there. I just killed one of the lunatics in there and as the body fell, I was hearing footsteps really close. I swung around ready to fire but there was nothing there but the footsteps were all around me. I spun around like mad hitting the right bumper to trigger VATS as soon as there was a target but there was nothing. STILL the footsteps were there, "the beating of that hideous heart" type thing. I didn't remember any invisible enemies other than in the expansions but I fired a few rounds towards the sound in case there was someone with a stealthboy there but nothing and still the footsteps. I was thinking there must be audio hallucinations when, with a rather foolish feeling, I remembered that my xbox controller's left stick has an ever so gentle drift and it was actually my characters footsteps. She was moving so gradually in so open an area that I just didn't spot the tiny movement of the viewpoint.
I said wow then, just that it was followed by "you're a bit thick aren't you?"

One thing I didn't quite understand about the vaults is - they're all experiments and most of them failed horribly shortly after they closed (apart from the one that didn't close, that is). So why in Fallout 3 are some of them still populated by vault dwellers who are completely batshit? Surely these people would have died out decades ago due to their inability to sustain themselves, raise children etc?
That aside, there were some great moments in the vaults. Gary. Gary? GARY!

Clive Barker' Undying: The plot twist (I won't say anymore about it either) and Lizbeth's room. I remember walking in there, looking around, then hearing "SCRYE" in front of a picture of her with a dog. I used the scrye feature and I was shown an image of her as a demon with a hellhound, with growling and snarling coming from behind. Also the mothers bedroom with the blood on the sheet.
Thief: Running for my life from zombies in the catacombs and old quarter.
Thief 2: The entire last mission.
Thief: Deadly Shadows: The cradle.....the horror....the horror....
Vampire the Masquerade - Bloodlines: The Ocean House hotel. For an added bonus, when you enter the hotel, go all the way to your left, then down the small hallway all the way at the end and to the right. Now turn around and try not to piss yourself. Also, the entire "tape" quest line was insane, fun, and extremely creepy at the same time.
Condemned 2: Bloodshot: The bear. RUN FOR YOUR LIFE
Clive Barker's Jericho: The first time you do the time swap and find yourself going from a desert, to a very dark and bloody alternate area.
Shogo: When I got the Juggernaut. Ka-vree BOOM
Gabriel Knight: Sins of the Fathers: Delving deep into the voodoo murders, seeing tons of people go crazy or die, and finally seeing what happens to Uncle Wolfgang.
Those are just the few I can think of off the top of my head.

Man, that's the truth, isn't it? You come from a bunch of WWII fighter plane sims dressed up in spaceships, then pick up I-WAR and get completely schooled. It's like going from playing Battleship to actually having to captain a battleship. BIG DIFFERENCE.
I *love* the I-WAR series, though -- even the sequel. Open ended space piracy. *Yum.*

For me the most exciting moment was escape from a hotel in Call of Cthulhu: Dark corners of earth - very intensive experience :D

The original Call of Duty for the PC, the car chase where you out the car window with a tommy gun. EXCELLENT!
When I first stepped out of the vault in Fallout 3, the view, just WOW....
Early in Gears of War when the giant spider thing erupts out of the ground while you are running for the helicopter, whoa, that's big MF!
FreeSpace (1) when the Lucifer shows up for the first time, OH SH!T!!!
Jedi Academy, fighting the bad guys on the high altitude, high speed train riding the lazer rails, AWESOME!
Early in DooM3, when you go to get the freaked out scientist just as the demon break out, GD CREEPY MAN!
Rome - Total War with the CAESAR IMPERITOR! mod, massive battle betwen my two full stack Roman Armies against three full stacked Barbrian Armies. After the battle was done, I lost 2,000 men vs over 10,000 barbrians killed, I AM CAESAR!
Medal of Honor: Allied Assault, The mission where you have to place the markers for the bombers to bomb the dam, the dam itself was amazing looking when veiwed from the ground! SPECTACULAR!
TIE Fighter, when your "allies" turn on you, TRAITORS!
there are more but I better stop now ;)

That was intense, but I think the battle with the Shoggoth or Dagon beat it out by a longshot.

One awesome moment was when my neighbor and I were trying to beat Maniac Mansion on the NES. All we had to do was access the locked door in the basment, but could not find the combination anywhere. Out of frustration, I just hit all 0's and it opened. We jumped up and down in sheer delight and watched the awesome ending!

Call of Duty: Besides being a pretty sweet FPS, the sound of the game is amazing too. Also as far as a WOW Oh shit moment in COD4 with the nuke going off.
Freespace 2: Pretty sure its this one, but when you and your wingman/commander jump out of the nebula and as soon as it loads you just hear "Dive! Dive! Dive!' as a Shivan ship is going through the node.
Operation Flashpoint: Its been awhile but right after the island is invaded and you get separated from your squad. You have to make you way to an extraction point on your own but you cannot just make a run for it. Just that whole fear while you're waiting in the woods for the BMP to pass and hoping a heli doesn't spot you so you can make a mad dash to the next stand of trees.
Plenty of others though.