Uncharted is packed with them. But the one I instantly thoughty of was in Final Fantasy IX, Bahamut VS Alexander. Also all of Weskers fight scenes in Resident Evil 5.

Oh and the Last of Us, spoilers.....Joel in the final part of the game, especially the cabin interagation. Also  a lot of Resistance 2, walking out of the compound in San Fran seeing the city under attack or the twist ending.

1) The Death of Luca Blight in Suikoden 2.

2) The Paris Kagekigan getting shot off the Giant Revolver Canon in Sakura Taisen 3.

3) Kazuma Kiryu vs Akiyama & Tanimura in Yakuza 4.

The bridge scene in ICO. It left me completely stunned. I just sat there, staring at the screen. I'd never experienced anything like that before in a video-game.

Back in vanilla WoW, when I first reached Ragnaros. He was so massive and powerful. It was overwhelming. Nothing afterwards touched the feeling.

The hollow husks of dead Space Pirates and Marine Troopers in the spaceship Valhalla in Metroid Prime 3. They desintegrate to dust when you touch them.

I remember this one scene from Metal Gear Solid. You enter a corridor that has dead soldiers in it and after passing it you fight that cyborg Ninja dude. Not sure which MGS it was, I played it on PC when I was a little kid and it left a huge impact on me. The whole game in fact, also felt sorry for Sniper Wolf. And who can forget Psycho Mantis? Sadly this is the only MGS game I ever played. Is the series still this great? 

I still think the moment in uncharted when your fighting inside a falling building was one of the most epic scenes i have witnessed in a long time

Despite how poorly optimized the game was (which often caused unfair deaths and frustration), I have to say the 59th Street sequence in Alone in The Dark (the newest one) was awesome, where you have to escape the impending death from an earthquake that's devastating the area surrounding Central Park.

So many to list. But got a few in mind.

Opening credits+ scene for FFVIII, the music meshed so well.
Wedding crash FFX.
First few areas of Onimusha were really nice.
Uncharted 3 Ship.

Quite a bit actually:

- ending of Red Dead Redemption
- Ellie boss fight (most intense moment in the game IMO)
- MGS4 end boss was so fitting
- Uncharted 2 train level
- the towers in DeS (terrifying)
- GTAV's map