Mine are the machine/wormhole/other planet scene from Contact, the entrance, destruction and exit of the Death Star in Return Of the Jedi, The “stargate” scene from 2001: A Space Odyssey, and the crash of the Enterprise D saucer section in Star Trek: Generations.
In the Series Finale for the Next Generation, Crusher’s ship is caught by the Klingon’s and is getting womped but good. Just as it looks like it cannot get any worse- another ship decloaks-
But, surprise, its the Enterprise- with 3! warp nacelles and one most excellent phaser system which procedes to blow a BIG hole in the Klingon ship. Admiral Riker saves the day! (Front row!)
Similar scene in B5 when the Earth gov ships are close to ready to destroy the B5 station after a long very hard fight- and lots of Mimbari ships start appearing close to the station and Dalen lets the earth forces know whats what.
Oh- add in the climatic fleet engagement in ST:DS9 between the Dominion fleet and the Federation/Klingon/Romulan Fleets.
the Mutara Nebula battle between Enterprise and Reliant
Qui-Gonn and Obi-Wan vs Darth Maul, even though the rest of the movie sucked
Luke vs Vader, both times
the endless pan across V’Ger’s surface in Star Trek I (ok, not really)
power-loader Ripley vs the Alien
in Starship Troopers, the scene where starships are getting blown up by Bug Ass Artillery
Unicron’s transformation, Transformers: The Movie. Has to be seen in a widescreen theater for the full effect.
The final sequence of of the final episode of ST:V was pretty good, if anticlimactic.