Wizardry VII: This game his my formative RPG, but allying with the Umpani and getting your hands on guns was one of those "whoa" moments, where the game really seemed to be bigger than your computer screen. Figuring out the maps, which were half clues and half philosophical mumbo-jumbo. Becoming crazy powerful and just tearing up enemies which used to require careful tactics and lots of reloads. And of course, cheerleaders on red rocket sleds, bless 'em.
The Savage Empire: Figuring out you could craft useful items like grenades and dynamite sticks out of materials you could find in the wilderness. Figuring out you can then use these items to solve puzzles in the game. Realising how goddamn big the game actually is.
The Serpent Isle: The trial in Fawn. Yes, in hindsight, Serpent Isle is kinda a step back from the previous Ultimas due to its linearity and the focus on turning you into a spectator for cool scripted scenes... but damn if those scenes weren't cool at the time. And gradually relearning to cast spells was also something special. Then, a much less satisfying experience from the ice fields and on.
Morrowind: Learning to fly was one of those moments which don't happen in most games. Joining the various organisations felt like actually getting ahead in the world. A quest felt like a quest and a pilgrimage like a pilgrimage. Navigating by landmarks was a big part of this, I think.
Bonus: The Lords of Midnight: It is The Lords of Midnight. You are dropped right into the middle of The Lord of the Rings, and the fate of the free depends on your ability to outfight or outwit an opponent who has already mobilised his vastly superior forces, and is moving across the mountains under the cover of his ice fear. Most likely, you will die trying, but damn if you aren't going to put up a good fight.