Definite +1 on Oblivion, even before I left the sewer. I remember the first time I bumped into the shackles hanging from the ceiling by a chain and seeing them swing around realistically, and thinking, "wow, this is really something new."
And +1 on SoTC - riding the horse out of the temple into the sunlight for the first time, or jumping onto the 1st collossus and seeing all that fur... then the camera swinging around wildly as he tries to shake you off. Incredible. They really need to do a high-def remake of this game.
Going back to older, less obvious moments - has anyone here ever played a PC game called Giants Citizen Kabuto? I'll never forget the first time I ran off a cliff in that game and hit the jetpacks, then landed in the plains below with all the wildlife running around me... It would probably seem quaint now, but it was a total headrush then (late 90's?)
I also vaguely remember a game called Drakan from the same period. I remember it's being so-so overall, but there was one moment running out of a cave into the moonlight and jumping into a body of water, and just getting an overwhelming impression of "being there". Those things stay with me.