[ConcreteRancor]

Serious Sam: Seeing the final boss for the first time, watching him smash his way in a matter of seconds through the entire level I just played through.

Half-Life: The opening scene, with the attempt at creating a resonance cascade. Then everything went to hell, and I thought: "Well now, here we go." And I didn't even have a weapon yet.
Another Half-Life moment was getting to the level "Surface Tension," and finally getting outside for the first time. Then I thought: "Oh wait... helicopters."
And of course, the level "Blast Pit," with the tentacles that couldn't see you, but could HEAR you.

Grim Fandango: The first ride of the Bonewagon.

Deus Ex: The first level, realizing all the different approaches I could take. I actually defeated the purpose of the game design by trying to do ALL the approaches, thus seeing everything and getting all of the skill points that I could.

Far Cry: Pushing barrels down a hill at enemies and watching the physics engine in action.