Of those that i could remember at the top of my head:
Final fantasy: there are so many but If i had to choose 1, i'd pick the opera sequence of FF6. others i really liked were: the beginning of FF6, when shadow rescues you in ff6 in the floating island, the ending of FFX, the big school battle of FF8, the beginning of FF12, the big reveal in FF7 that cloud was that random soldier and that he defeated sephiroth.
Chrono trigger: When you enter the future world and see all the devastation. frog's flashback and he splitting the mountain.
Suikoden2: the massacres sequences of luca blight is my top spot, when you finally kill him is a close second, in general the whole back and forth between the 2 armies was awesome in that game. also gremio's death and queen arshtat and ferid's death in S5 were also memorable.
When glados betrays you and then when you escape her grasp and hear her freak out.
The ending of Shadow of colossus and battling all those colossus in general.
The final fight with ganon in windwaker.
the mecha fight in MTGS4
The destruction of Karak in Homeworld
Silent hill 3: there was a sequence in that game that really freaked me out. it was in the hellish dimension and i saw in a bathroom as something in the reflection of the mirror starts to crawl up me but not in my real character them when it consumes my reflection it starts to attack me and the whole room goes dark. that shit just freacked me the fuck up.
Losing for the first time in the original XCOM
The ending sequence in oblivion.