Zelda: OoT - when Link removes the Master Sword about a 1/3rd of the way through the game.

This even marks a sort of paradigm shift in Ocarina of Time. After it, the game's setting becomes darker in nature, the story goes into full swing and most obviously Link becomes an adult, opening up lots more gameplay possibilities. IMO is probably the best plot twist in a game, comparable to 'that' moment in Chrono Cross and similar events in Final Fantasy VI amongst others. I loved that moment when I first played OoT about 10 years ago and even now it is one of my favourite moments of any game.

Really? I can't decide if that's awesome or crazy. But given my daughters are named after a demoness and a water spirit, I'm hardly one to talk.

EDIT: Also: not Jason? ;)

One of my favorites is definitely the final level of Metal Slug 3.

It's bat-poop insane. Talking about it would sound like the blabbering of a deranged old man who belongs in a straitjacket. And watching a video doesn't really have the same effect as playing it yourself, so all I can do is heartily advise you to go out and BUY METAL MOTHERFUCKING SLUG ANTHOLOGY.

Here's how to do spoiler tags, btw. You can see through it if you look at it intently, but if you deliberately look at something you don't want to know, you only have yourself to blame for getting spoiled.

While, yes, I could do that, I am very, very lazy. ;)

Let's add a few more of mine:

Shogun: Total War: This was another revelation game for me. The huge battles blew me away. My gaming moment is the first time I completed the campaign without quickplaying a single battle. It was Epic. It took forever. It felt awesome. No Total War game since has ever really captured that feeling again, although Rome came bloody close.

Magic the Gathering: Duels of the Planeswalkers: This is an okay game, but it had an unexpected gaming moment for me: it's the first (and only) game in wheich I saw another person naked. Luckily for me (given my preferences), it was a girl (and not unpretty). It seems she didn't expect the Eye to blast her image across the internet when playing online. She clearly got a shock. So did I. She quit out. I then warned my wife (who plays the game), and she wisely made sure she was fully clothed the next time she went online.

SingStar: The first game I played with both my kids at the same time. The videos are online in the game, and we're terrible singers. My gaming moment here is my daughter's video being in the top 100 videos for over a year, earning her a Gold trophy for getting so many views. She was so, so pleased.

You know, thinking about it, I think my all-time favorite moment in any interactive entertainment product might just be finally beating this cheating son of a bitch in Super Street Fighter II Turbo as a 12-year old.

God, I spent my childhood on that guy. The fact that my 16-year older brother (who had played the game even more than me) couldn't pull it off made it even the more sweeter.

Either that, or one of the countless nights I stood up with my cousin playing Marvel vs. Capcom 2 until the sunrise.

Zelda OoT - When link first pulls the Master Sword (I see someone else mentioned the same thing)

ICO - Running across the castle battlements and looking out at the landscape (First time I thought games could be art)

Valkyria Chronicles - Isara's death and the impact it has on Rosie

Another few of mine:

Elite: I had this game for a long time and rarely got very far with it, primarily as I was a kid at the time and had little patience. My gaming moment in Elite is finally buying my first docking computer, meaning I didn't have to guide my ship into the space stations by hand. It was an utter revelation in terms of making the game more enjoyable to play. I know this may appear a minor thing, but I ended up playing Elite a lot, and I never would have without that damned docking computer. I can still hear the music now...

inFamous: I loved inFamous. Many didn't, but I did. Then again it was where I scored my first Platinum trophy (a true gaming moment), so I may be a little biased.

Space Hulk: This game was rubbish, but I worked for Games Workshop at the time so I gave it a lot of slack. In particular, one moment stands out for me to this day. It's a simple incidental sound bite while creeping through the corridors avoiding Genestealers: 'I smell something! No, wait, it is only Ezekiel.' For some reason that is hard to fathom now, I found that hilarious.

WWE Smackdown vs Raw (I forget the year): What, a WWE game has a gaming moment? Well, yes, it does. I created a perfect reproduction of myself using the Create a Wrestler tools and proceeded to win every belt I could and the Royal Rumble. It was oddly awesome.

Super Mario World: I adore this game. Utterly adore it. My gaming moment here is 100%ing it. The first game I ever scored a 100% completion on (for games with such things). Not long after, I sold my SNES.

Mario Kart: Double Dash.
My first videogame ever.

Chrono Trigger.
All

Metroid Prime. Duking it out with meta ridley.

Legend of Zelda Wind Waker. Defeating phantom Ganon.

Halo 2. CTF on Blood Gulch, or w.e it was called in halo 2.

Phantasy Star Online Episode 1 and 2. Beating the final boss of episode 1...