Morrowind is just such an epic game. There were a lot of moments in that game for me. OMG, the Tribunal expansion pack was ridiculous though. So many great fights, so much awesome level design.
I still play it. I remember the summer after graduating uni, just playing morrowind non-stop. I must have literally spent over 1000 hours playing that game.

Fallout 3: Top of the washington monument at sunrise looking out over the devastation. Really nice/horrible view
Hitman Blood Money: The level Curtains Down was just amazing and exemplified the genre for me. Massively public setting, 2 horrible people who need to die, the sheer artistry of the kills you can accomplish, tosca...
Oh yeah, who didn't sit there with their jaw on the floor the first time the loaded Shadow Of The Beast? Gorgeous graphics & music. Sure the game was pretty simple but it was a solid fun game with some of the most amazing visuals of the era.
FA18 Interceptor. Filled polygons were still amazing and it started my lifelong love affair with flying inverted under bridges at maximum speed. Easier to do in an FA18 in that game than it is in a HE111 in IL2
Syndicate: "What do you mean "am I still up", its only been a few hou... er... is that the sunrise?" (this also applies to civilization and pretty much every game that sid meier was involved in)

Shenmue (1999): Holy balls the size of this game. This is epic!
Batman [NES] (2007): I can't believe I've never played this before! THE MUSIC! OH GOD! THE GAMEPLAY!
GoldenEye 007 (release): AGHH STOP CHOOSING ODDJOB! FPS GAMES RULE!
Super Mario 64 (1996): My jaw just hits the floor, basically. 3D GRAFIXXXXX.
Wii Sports (2006): My god this is such advanced technology it's like I'm really playing tennis and look I'm burning 3 calories or so wow this is amazing...for now.
Super Mario World (1991): Oh my goodness my own video game and it turns on holllyyyyy awesome.
Donkey Kong Country (1994): Intense graphics and giant animals to slay.
Mega Man X (1994): THIS IS THE NEW GREATEST GAME EVER MADE.
Star Fox (1993): :OOOOOOOO
Super Mario RPG (1996): Most epic game ever made.
Mega Man 9 (2008): 8-BIT LIVES!
Mirror's Edge (2008): 1st PERSON PARKOROAORKAROGROARGMAH
Metal Gear Solid 2 (2008): Why the fuck did I discard this game as forgettable upon release? My god, the attention to detail!
Metal Gear Solid 3 (2008): And it continues! EVEN BETTER. PERFECT EVEN! EPIC STORY!
Metal Gear Solid 4 (2008): SECOND PERFECT RELEASE SINCE DEUS EX!
Braid (2009): Deep.
Crysis (2008): You can really just imagine my reaction upon hitting this baby up in my new computer.
Deus Ex (2005): Every twist had my head exploding by the complexity and attention to detail. Initially disliked @ release. Now see it as the greatest game EVARRR.
Doom3 (2004): Just the graphics, really. Mediocre game. It was at Doom 3's release that I started making stupid comments every now and again about how great the graphics were while I wasn't anywhere near a video game.
GTA III (2001): Same reaction as everyone else. It's aged badly though, IMO.
Half-Life 2 (2004): Blown away by the game's brilliance, and the gameplay, and the story, and everything. Jesus. The physics.
Mass Effect (2009): One of the few PC elitists actually blown away by the game. Sure, it's story was cliched and predictable ("They say the citadel [or wuteva] has all these stairs in order to defend against a direct attack!" ADURRR I wonder if the end game takes place here????), and the Mako sections blew absolute shit, but the game's world wow'd me as I haven't been wow'd by sci-fi since I saw Star Wars as a kid.
Max Payne (2001): OMG BULLET TIEM.
STALKER (2007): Auto orgasm for a fan of Tarkovsky and Strugatsky bros. and...uh, history.
Soldier of Fortune (release?): GOREEEEEraeraugh.
System Shock 2 (2001): Scariest fucking game. Ever. Took me 6 years to get past the beginning zombie-filled corridors. Six...fucking...years....
Morrowind (2003): The graphics were so flipping good. And that water. The first water that truly had me drooling, especially during rain with that epic music goin'.
Zeno Clash (2009): JODOROWSKY VIDEO GAME.
&c.
I wrote a lot more than I wanted to. Good waste of time!....=/....=(

This is what I noticed about Mass Effect, the plot is cliched, and several gameplay mechanics are absolute shit, but the dialog, and the world, and lots more are just so well-done that it makes it all worth it. Even a completely predictable plot can seem amazing as long as the writing is good enough for you to care about the characters.

Homeworld
A mothership launch and final mission (along with stunning music score).
Planescape: Torment
Intro. Finding truth about shared past with some story characters (companions and others). Curst. Ending.
Beyond Good & Evil
Ending.
Fallout
Intro. Necropolis. The Glow. Cathedral Vault.
The Longest Journey
Prologue, first look on Alchemist's Castle, ending.
Dreamfall: The Longest Journey
Ending (I know - lots of pointless cliffhangers; but I still liked it :-) ).
Clive Barker's Undying
Meeting Lizbeth.
Realms of Arkania 3: Shadows over Riva
Riva's sewer system events.
System Shock 2
Some datapads and Shodan.
Operation Flashpoint
Evacuation from Everon.

Chronicles of Riddick: Assault on Dark Athena: Playing the 1st game with enhanced graphics, and using a screw driver to stab a man in the eye while his body tumbles into the trash disposal thingy...
Earthbound: When I fell in love with the shitty writing. Seriously, did a 5 year old right this? Cute though.
Fallout 1: I experienced my first old school nostalgia.
GTA 4: Like Alias said, but with music turned off and blasting Pantera.
Uhmm, I can go on but I'll stop for now.

most of mine were mentioned already, but whatever:
planescape torment - whole game, my all-time favourite
command & conquer 1 - install, intro, choosing sides (GDI), first briefing, video, deployment with act on instinct......it just flows. the first moment you hear act on instinct and the sound of that gunboat firing at turret....ow.
medal of honor allied assault - omaha of course
unreal tournament - intro
dune 2 - intro (THE NOBLE ATREIDES!...and so forth :P)
jedi knight 2 jedi oucast - level design of imperial bases. add kitchens, toilets, lavatories and you could beleive that someone actaully uses it :).
knights of the old republic 2 - kreia, i love such characters. who cares for chaotic evil. it floods us in pretty much every fiction, be it TV or game. and finally someone that could stand up to ravel puzzlewell (planescape torment). neutral evil/true neutrals, thats it :).
freespace 1 - first encounter with lucifer and its destruction.
freespace 2 - blowing sun :) (last mission)
max payne - dreams
prince of persia: sands of time - they actually did it? and did it THAT well?
thats pretty much everything i can remember now.

Can I also add Earthbound to this list?!
I only played it fairly recently, and my WOW wasn't so much "This is going to change gaming!" but, "wow, this game is so good!"
It has such charm! And I love that it has a sense of humour. Beautiful graphics too. I'd quite happily play modern games with those same graphics.
So yeah: Earthbound!

for the record, since my last post I have played and beaten xcom again >_> damn that was fun!

There are too many to list right now, but one of my biggest wow moments was watching the opening scene to Mafia...the music is incredible.

I think the biggest wow for me was the return after the first hyperspace jump. I'd never heard Adagio For Strings before then and damn did that make the moment...

CoD4 - As it's been said, the nuke scene. But also the ending...
Fallout - The non-combative way to end the game.
Morrowind - Go wherever I like? Do whatever I want? Yes please!
Medal of Honour: Allied Assault - Omaha beach, enough said.
Portal - Such an awesome game, excellent puzzles, awesome atmosphere.. It really had me hooked to the last slice.
That's all I can think of at 4am.
EDIT:
Final Fantasy 7 - Brilliant game, stunning FMV.
Resident Evil - The part where the dogs jump through the window, near shit myself.

Ultima 6: The world was huge for a little boy who learned english so he could move forward with the story.
Fallout 1: First rat spilled it's guts on the cave floor I knew I was in for a rough ride.
Final Fantasy 7: Animations were fabulous and world just blew me away.
Homeworld 2: What else could a nerd want but 3d rts in space with unearthly musical score. The scenery was also good and I found myself just looking the nebulas while I should have been commanding my ships.
Rome Total War: Oh look at those little buggers whacking each other. And how many there are. Go my minions, do my bidding and conquer the known world.
Red Faction Guerrilla: I giggled like a little girl when I realized that all buildings can be demolished. Then I giggled some more every time I got bigger gun. Needless to say, my gf my giggling amusing.

I don't think I've seen mine anywhere.
Super Metroid. The first one was descending into Zebes again for the first time, and going "Holy crap! I've been here before." But the biggie was the ending. When the baby metroid swooped in and grabbed Mother Brain, then sacrificed itself to save me, I was amazed. I'd never had an emotional response to a video game before besides frustration and happiness.

Ditto the Undying. Great, great game. Probably my all time favorite FPS. Doom 3, Powerslave (I'm an Egypt buff, admittedly it's not the world's best FPS) are really the only other games in the same ballpark for me in terms of fun factor.
Too bad it was clearly rushed at the end (well it was probably rushed after the first 1/3 of the game since the missions are so much shorter after that). Amazing boss battles, too except for the last one. That was really beat. I sooooo wish we got an Undying sequel instead of Jericho.