I have experienced nothing as satisfying as bringing down a Colossi in Shadow of the Colossus, as raskerino mentioned. After putting all that work into hunting the beautiful giants down, then figuring out how to eventually dominate them, and ultimately doing it... ahh.

An older memory sticks out prominantly in my mind of Donkey Kong Country. I was such a munchkin at the time, and my parents were totally into the game too. Everybody was bouncing around after I beat King K. Rool, up until somebody pointed out after the credits rolled it said "The End?" And all of a sudden he bounced back to life and kicked my butt. XD That was great.