My favorite moment in the LotR's; was when Aragorn turned to the King, in his darkest most hopeless moment...... and said, "Ride Out with Me". To me, that was when he earned the right to lead. When everyone else was in a panic, and it seemed that all hope was lost; Aragorn rose to the occasion.
Are you talking about stuff at "my" gaming table? If so, while not awesome, one of the most fun bits of revelation was when players were in a sailing ship in a little bit of a scrap. When one of the players pushed an assailant off the ship, they didn't hear a splash. Clearly this was a Spelljammer ship and the characters should have known this, but I withheld that information from the players just to see their expressions. And it was wonderful. (And they enjoyed it.)
The rest of the game? Not so much. It was my first time with GURPS 3e and I was mashing together what was then setting-specific realm-based magic with normal skill-based magic (meh) and it was... Messy. Fun, but messy.
The example that you used was from cinema, so for one moment of bad-assery from literature, in this case from the Riftwar Saga, I'm reminded as an 11-yo reading of the time when Milamber finally came to terms with his two-world heritage and gained access to his full power. Tearing aside the barriers in his mind and, witnessing the slaughter of slaves/innocents in the colosseum, he called down the elements and quietly whispered (but all heard), "Tremble and Despair, For I Am Power".
Sure, preteen power dreams but I still remember it to this day.
My example is actually Middle-Earth related long ago, before the movies came out, but everyone had read LotR and put quite a bit of effort into their characters. The PCs were looking for an old Palantir-like artefact in a ruined castle (actually a crystal skull, no relation to Indiana Jones). They had long suspected that someone else was hunting for the same thing. At some point they realized that their pursuers were led by a Nazgul, who was not far away. One of the players turned pale and yelled "oh no, we're going to die!", and suddenly everyone were personally immersed in a way I have rarely seen.