metal gear solid 2-getting those creepy messages near then end of metal gear solid two always tripped me, mainly because i had been playing for several hours, and the suggestions to turn off the game made too much sense. pretty much every boss fight in metal gear solid 1-3 amazed me. especially the lead to first finding that ninja guy, when he slaughters a bunch of people.
resident evil: code veronica- finding out that getting on the plane and killing the monster that had followed you was barely getting halfway through the game.
morrowind- the first time i saw one of those nech things. or pretty much anytime i got attacked underwater.
kotor-spoiler kinda!
both games were amazing, though what always blew me away, was when you realize just how badass your character is, when you find out who you really are. in part two, what was pretty crazy is how effective a story can be even when you can easily guess who the bad guy is in the first few minutes of the game. then seeing just how much control she has over others, and still sorta worships you. for me these game blew away anything the movies did.
red faction- testing just how far a rocket launcher will dig into a wall, something no recent game that i have tried has been able to match.

Hmm, perhaps it was when I realized who Sarevok was in BG1.
In System Shock, hearing Shodan call me an insect made of flesh and bone :D
Deus Ex, hearing Daedalus , that was like whoa...
Doom - the cyberdemon :D
Those soviet walkers in Battlezone, those were really awesome (and scary)
That crazy cow level in Carmageddon. So twisty and insane!
In City of Villains, beating the Leviathan boss (massive, black eyeball that shoots ... things... at you)
Dark Messiah of Might and Magic - the awesome spider part, where they all crawl down. Wow, that made my spine tingle just thinking about it.
Doom 3 - seeing the imp crawl out from under the stairs. It was completely unexpected and scared the **** out of me!
Duke 3D - Football stadium boss. Was really, really, really awesome
Dungeon Keeper 2 - Realized I could smack my minions around, lock them in rooms (forcing them to work), torture them and make them fight in pits!
Grand Theft Auto 2 - Finding the tank. 'Nuff said.
Heretic 2 - Really enjoyed this game. The traps you had to get through made me go "wow!"
Morrowind - When I realized the vastness of the world. Amazing game
Myst - Figured out that stupid maze puzzle. *that* was cool
Netstorm - Sacrificing another priest. I dunno, it was just SO cool. "For the wind, for the sun, for the rain, for the thunder, it is done! EAARGHHH!"
Neverwinter Nights - Hordes of the Underdark - that awesome war part. Wow, it was definitely the pinnacle of games
Serious Sam - Too many to list! Holy crap, this game totally blew me away with epicness the whole time. Two parts that stick out though was this crazy twisty tunnel section in SS:SE and the ice fortress in SS:SE.
Sim City 2000 - Poppin' down my first arcology.
Red Alert - the intro.
Starcraft - When Kerrigan returned :p
Planescape Torment - Yeah... too many to list, though some parts that were awesome were when I paid some old lady to 'search' different parts of me for trinkets. Guts, skull, stomach, you name it.
Worms Armageddon - Dropping the ol' Concrete Donkey the first time >:D

Farenheit: From its gameplay to the story. I was like WAOUW! all the way.

Agree, that was also amazing "wow" moment. But I still like more previously mentioned moments . :-)
I think whole game - except last chapter (Eternal autumn) and two last boss battles - was fine. But first and probably the longest part (with Lizbeth) was really the strongest. And because of it is Undying one of the best FPS games for me too).

Pro Pinball: Timeshock! - Realizing why that chariot soldier on the right outer lane has his head lend to the side.... that is ATTENTION TO DETAIL!

half life 2 ; while being stuck in the teleporter and you are suddenly in the ocean face a big as fish with teeth the size of a lanternpost.
assasins creed : the first time you eagle jump
the witcher : face 2 face with 4 ghouls the moment i swallowed a cat potion and the suddenly appeared in front of me.
+1 on the serious sam : the epicness of the levels where indeed unmatched so far.
LEFT4DEAD : the intro + first time facing a tank
...

Red Faction Guerilla --- The first time a building you half damaged breaks under stress, your jaw will drop at its amazingness as mine did.
GTA San Andreas --- This was my fifth GTA I ever played, and the amount of content with the incredible graphics (of the time) all on the PS2 made me just squeal (which I do not do often) in awesomeness.
inFamous --- The lightning storm, do I really need to say more? Ok, you can combine your powers into combos for amazing results.
Any game in the Elder Scrolls series --- They just keep finding new ways to impress me, and I love all of it
Half-Life 2 --- The first time I played this, the Source engine was 1 year old, but it still looked good. The fact that even at low the character models looked great was just... amazing.
and for the grand finale
Half-Life --- Who couldn't deny that the first time you popped this b**** in you were expecting a quake clone, or a straight run and gun. I was, but then you learn the game has a story, and a good one at that. That there were zero cinematics (like real life), and all was in your perspective. Don't tell me HL did not wow you at the time. There is a reason people still play it to this day.

Actually I was distinctly un-wowed by half life, guess I'm the exception to the rule

Not really. I thought HL was a good game but it didn't make me say 'wow'.

Just had another one in Fallout 3, seeing the earth from the big viewport in mothership zeta. Really nasty picture of destruction
Just had another: The END of Mothership Zeta. Damn pretty explosion

My greatest WOW moment: in Star Control II, having to wear down the defenses of the Kzer-Za megaweapon. One by one, my fleet was being systematically annihilated. It looked like a losing battle ...
Until, just when all hope seemed lost, a squad of Pkunk Furies showed up!
And then the Pkunks began resurrecting to the Hallelujah chorus.
*That* was a great WOW moment. Very tense, made total sense in the context of the game, and felt totally heroic. Like dropping a proton torpedo right into the heart of the Death Star seconds before it would have destroyed Yavin 4. Awesome.

I'm wowed to see someone else here liking Fallout 3. Haven't tried Mothership Zeta or Point Lookout yet, and only barely started Broken Steel. Which is mostly because I'm pissed off at the crashing problems I've been having, which is probably due to one or more of my mods, or potential faultiness of the later patches.

I NOW HAVE FULL ACCESS TO YOUR SYSTEMS

Playing GalCIv2 on one of the easier settings I was building up my military on the border of one of my rivals. They then contacted me telling me they knew exactly what i was doing but they couldnt do anything about it because of the difficulty i had the game set on. That was a total OHWOWLOL moment that had me practically fall off my chair laughing.
I then started a gaeme on a higher difficulty.

As far as I'm concerned its a remarkably faithful game in the fallout universe and well worthy of the name. Never truly understood the anger over the game beyond the usual "ooh its different" & developer fanboy bullshit. Hell if star wars can have half a dozen different types of games set in the same universe why the hell can't fallout?
I've only played anchorage and zeta so far, bought them all on friday but its taking awhile to get to a level where it felt like I could do the addons, now I'm so tooled up with fancy alien gear that the rest of the game will probably be easy.