Shadow of the Colossus pretty much fills the top 5 for me.

Other notable moments include
MGS4 Mech scene, awesome surround sound.
NFS hot pursuit, engaging the turbo, still the best sound effect to date.
GT5, sunrise while racing on the Nurburgring.
Wipeout series, first person mode sideways through the corners corkscrew down a cliff on a projector.
Elite Dangerous, first time departing with a fully loaded T9, pretty much entire bank balance on the line.

This./

I´ll put all the moments I can recall (in no order)

Escaping from the cops in gta san andreas after going HAM.

Beating event 51 in super smash bros melee.

When Dyna Blade appeared in kirby air ride.

Super metroid: mother brain battle.

Metroid prime: arriving to tallon overworld for the first time, with that freaking amazing music.

Metroid prime 2: the first fight against dark samus, that cutscene and that music god damn so epic.

Super Mario Sunshine: all sections without fludd.

Legend of Zelda Twilight Princess: Ganon´s castle.

Legend of Zelda, skyward sword: horde battle

FF7: escape out of shinra tower.
MGS: psyho mantis fight.
Super mario world 1 and 2: bowser endboss.
Eternal sonata: gave me all kind of emotions.
Lots more deserve the fifth place. :p

Man, I couldn't agree more with your choice. When I was a pokelad, finally standing toe to toe as equals with Gary was amazing. The music just makes it all the more epic. It was a pivotal point for me in gaming.

Oh my gosh, I forgot about Paper Mario: Thousand Year Door! Call me a big fat dork, but that last battle literally gives me chills just thinking about it. Gosh darn, what a fantastic game.

Pretty much the final boss of every game.

Max Payne and Kingdom Hearts.

At first I thought it would be a chore to come up with a Top 5. But now I'm struggling to keep the list to go up from 5.

Mafia - When Sam says his last words on how Tommy's efforts to keep marked people alive were in vain.

The Last of Us - When Joel rescues Ellie from the hospital and kills all the fireflies. And then the ending...

Red Read Redemption - When John travels to south of the river on horseback.

Hitman Blood Money - When I get into The White House and Secret Invasion plays.

Uncharted 2 - The end of the train scene and when Elena gets badly hurt by the grenade.

Uncharted 3 - The entire cargo plane scene, when Nate gets thrown out the back, when he goes back in and somehow blows it up, and gets flung out of the plane again just to grab a parachute crate for his life, so he could see the big problem he was in. The music helped a lot.

Metal Gear Solid 4 - "This is good... isn't it?"

Pokemon Blue Version - The Final Road.

5. Sonic Adventure 2's ending+credits+thefuckingpostcardthatsaidShadowwasdeadbutthenhecamebacksegawhydoyoudothistome.
4. Paper Mario, the whole thing.
3. Super Mario 64's game file selection menu. That music is just magical.
2. A draw between Kingdom Hearts 2's ending+credits and Portal 2's ending+credits.
1. Sonic Adventure 2's Hero Story intro cutscene, seriously, the guy breaks out of A FUCKING HELICOPTER, STANDS IN ITS WING, RIPS SOME OF IT APART, AND USE SAID RIPPED APART PART OF THE HELICOPTER TO FUCKING SNOWBOARD IN THE MIDDLE OF SAN FRANSISCO AFTER FALLIG LIKE 2000 FUCKING METERS, AND EVRYTHING WHILE SOME BADASS ROCK PLAYS IN THE BACKGROUND. HOLY SHIT. LIKE, HOLY SHIT.