I think the last time I played a section of a game that really gripped me while I was playing it (well, two come to mind,) were the first F.E.A.R. and the old really bad Wheel of Time first person shooter.
I remember one thing that made me jump out of my skin was seeing the little girl from the first FEAR (I seriosuly only played like two levels, not paying attention to the story since it was on my bro's comp, I didn't own it,) walking towards a ladder, she then disappeared in thin air once she reached it. Well, soon as you start climbing down, instead of usual FPS where you can face outwards towards the ground or jump down, you face the ladder...soon as you turn around the little girl is RIGHT IN YOUR FACE staring at you as you climb down the ladder. Freaked me the EFF out. Some parts were well done.
In WoT, there's two sections that were amazing atmosphere and fun. When you enter the city of Shadar Logoth, it's empty of all human life cept you and some big Trollocs, and a mist called Mashadar. Mashadar cannot be stopped by anything, it moves slow but nothing can prevent it from moving etc. So you have to be constantly on the look out for that, as do the monsters. You sometimes catch a glimpse of people in windows where they can't be, since the place has been deserted for countless centuries. The music and overall feel is oppresive yet entertaining, as you're always slowly peeking around corners then sometimes having to flee the mist.
The other section is the Ways, you enter a portal to get to them, and they're infinite. There's land to stand on, but it's like having docks hanging in mid air made of stone, that can go in any direction. There's no wind or life in the ways, cept humans or Trollocs/etc that wander in. The only thing that well...exists in the Ways is Machin Shin (The black wind.) It randomly roams the Ways, looking for things to consume. It doesn't eat them, just drives you inexplicably insane at the slightest touch. It's a gust of wind/wall of roiling black faces, those you recognize and those you do not, all calling out in tongues you can and can not understand, whispering in your mind. You can't fight it, and the only way to survive it is to exit the ways, which can be quite a problem if you get either lost, or deep into the ways with trollocs in your way to the door or a long distance there.
good stuff.