That was my favourite section of the game too. I can't believe I forgot to list it.

Oh, yeah. And that level just gets more intriguing. I kind of wish it had been more open - maybe have multiple, hidden objectives? - but overall it's an awesome level.
The level where you're trying to defend one of the destroyers or whatever as it retreats out of the nebula is also very memorable - you can't see the whole ship, and no matter which way you go there always seems to be more of it. The playful opening of the level (keep some experimental stealth fighters from winning in a war game) also helps make it memorable.
Overall most of Freespace 2 is pretty memorable. It's hard to pick a particular level. :P
As for Planescape: Torment... well, pretty much the entire game, I'd say. And if that's too much, then it's a tie between the final level and the Mortuary. Oh, and the Brothel for Slaking Intellectual Lusts.

Half-life 2 Ravenholm was pretty awesome, and most of the underground levels in STALKER SoC comes to mind.

The cafe at the opening of Indigo Prophecy.
Everything in Bloodlines.

Hm, I don't understand why I neglected to include Green Hill Zone from Sonic 1 as well, considering I did include Chemical Plant Zone Act 2 from Sonic 2 which is memorable to me, because it showcased the ability to gain an immense amount of speed right in the beginning, featured a secret shortcut that brought you straight to Dr. Robotnik, a tense couple of seconds where you struggle to stay ahead of the rising water and had an interesting boss. Besides that, I really loved the look of the Chemical Plant Zone in general and the way the zone seemed to be so intricate.

I just remembered my favourite level from Half-Life 2: Episode 1. I can't remember what it's called, but it's the one where you're in complete darkness and you have to use your flashlight to "paint" targets for Alyx to shoot. Best part of the episodes, in my opinion.

Metal slug 3: 1st level, when you realize the branching paths.
Best game ever!

A game called Trantor, which i played on the Amstrad pc.
As the game loaded from cassette there was a huge graphic of your spaceship landing which blew me away, (my age was still in single digits back then), and the large sprites plus a big blue alien which lurked on one of the lower levels were the best graphics and gameplay i had ever seen outside of an arcade machine.
And the game Aliens, the old version where you had a crosshair but the game scrolled from left to right. At some point in the game the power is cut and when you enter a blacked-out room and hear the alert noise signalling an alien, you have to fire your gun so a flash of light illuminates the area briefly; you get a few shots before the alien eats your face. Sometimes the flash of light would show the alien looming before you, going in for the kill. The first game that unnerved me and made me jump.

The battle with the sand dragon in Shadow of the Colossus was absolutely epic:
http://www.youtube.com/watch?v=dPyiY2C2-uc

Dark Carnical is defs the best campaign..

Shadar Logoth - The Wheel of Time.
The Ways - The Wheel of Time

I could do this all day, but I'll limit myself to ten.
"Anticitizen One" to "Follow Freeman!" is probably the best part of Half-Life 2. Ravenholm is great, but it doesn't quite compare to getting thrust into the role of resistance leader and finally taking the fight to the Combine.
"Shock and Awe" from Modern Warfare is unforgettable for reasons I won't mention. You need to see it for yourself.
Chapter 6 of Mother 3. Simple and beautiful.
It's certainly not the game's best level, but Hell's Kitchen in Deus Ex was what first convinced me that the game was something special.
The final part of "Smile" in Killer7, when all the pieces fall into place. It's one of the only levels that carries no risk whatsoever of failure--it's pretty much just walking from cinematic to cinematic--but it's the most fitting climax possible. When he opens the briefcase . . . damn.
Dungeon 9 in the original Legend of Zelda was a harrowing experience when I first played it. Far more than the previous dungeons, it felt like a genuinely hostile place that I'd be lucky to escape alive. Conquering it was a defining moment of my childhood.
Star Light Zone in Sonic the Hedgehog. Do games get any lovelier? If so, then not by much. That music.
I feel like I need to include something from Thief II, but picking just one level is an absolute bastard, because so much of it is just exquisite level design. I guess the one that most sticks out to me is "Ambush!" (the one where you have to make your way through the city streets back to your apartment). All kinds of tension.
1-1 in Demon's Souls. Really, the entire game is wonderfully put together, but that first taste of it does such a great job of showing you exactly what the game demands of you.
And finally, more a combination of boss and level, I guess, but The End in Metal Gear Solid 3. A boss battle that can easily last longer than half an hour and yet stay perfectly tense from beginning to end. Finally catching a glimpse of him just as he spots you, and then getting off a shot before he can--that never gets old.

Black's Dice Palace STILL makes me smile - that music is fantastic and the whole tsuguroku (table game) aspect is hilarious. Hell, Gunstar Heroes remains one of the best console games to date for any number of great reasons.
I've a special love for the first two Tomb Raider games (despite the dopey underwater level that somehow transforms to a huge cave stage with AIR while Lara is still underwater!). The level design was innovative and those traps...(remember the Midas stage? SURE, go touch that statue)
The rest of those picks are gems as well...
A few of my faves off the top of my head (some already listed with SPOILERS, sorry) - there are dozens upon dozens more, but here goes.
Half-Life 2: "We Don't Go To Ravenholm"/Highway 17 - Scary, scary stuff with some pretty inventive traps placed all about AND that deadly Mine section at the end. Hwy 17 is a total blast, as you've got wheels for the bulk of the stage and you can choose to blaze past some areas or hop out and explore a few locations to find... well, more enemies trying to kill you (damn Antlions!) and some physics fun.
Siren (PS2) "AKIRA SHIMURA Day 1 08:00 Gojaku Peak, Misumi Trail" (both attempts) -
Playing a character that's going to die (and not knowing this), this stage puts you in the shoes of an elderly man with a hunting rifle trying to escape a mining area while a number of undead enemies prowl about. Actually, all of Siren in a unique experience (and a number of the playable characters don't survive) that twists "survival horror" to new levels.
D2 (Sega Dreamcast) - Not a level per se, but returning to the crash site and seeing the plane wreckage is pretty frightening. On the other hand, the boss battle in the crazed musician's house against the guy's mother (who has mutated into a violin-playing metal masked winged creature!) is pretty memorable. For all the nastiness the game throws at you, you never see the face of the mom-boss after her mask falls off, making the reaction to her "unveiling" something different for everyone that plays the game...
Silent Hill (PSOne) - The opening sequence when Harry is running through Silent Hill on his daughter's trail is pretty straightforward stuff.... then you walk into that alley and the camera does that sweet cinematic tilt. About thirty seconds later, things get even weirder as it grows dark and everything changes into hell. The ending of the prologue, with Harry being trapped and (ouch!) knifed to death by a pack of creepy faceless baby-things is priceless because there's no way to escape and as you're sitting there screaming at the TV because your character just got killed... he wakes up in a deserted diner and there's an even more surprising coda waiting... and that's the beginning of a great game full of memorable shocks (cat in locker, body in locker, big ass cockroaches, monster-tracking radio going dead mid-game, Alessa, poor Doc Kaufman and so forth and so on).
Clock Tower (PSOne) - The first time you run into Scissorman (and every time afterward) - that music will make you pee your pants, especially when you're not prepared. Ten endings will keep you hopping back to previous saves, especially since most of the endings are quite horrific and you'll try for the "good" one 'til you get it (and even then...)
Earth Defense Force 2017 (Xbox 360) "Inferno" (on Inferno difficulty) - The penultimate stage of this arcade-style run' n gun throws giant ants, spiders, flying ships, bud-dropping saucers and huge death-spewing robots at you all at once and well, you need to survive it all in order to reach the final stage. In co-op, it's doable, but in single player, it's near impossible unless you've been farming armor (on stage 8,easy mode) for a few days beforehand. Unlike easier FPS or action games made for today's gamer, there are no respawns or save points if you die, so buckle in, knuckle up, bite the bullet and pray you get a few healing kits to drop during the carnage.
Virtual Hydlide (Saturn) The Lost Castle - Yeah, yeah, the game is quite awful (and painfully tough to look at and play), but if you look past the dated visuals and chuggy, chubby, stumpy hero, The Lost Castle stage sticks out for being visually outstanding compared to the rest of the game's pedestrian RPG trappings. That and the Dark Sword you may have found makes for some pretty cool light-draining attacks.

I love the bridge finale in The Parish. It's also pretty cool to recognize the architecture - I went to New Orleans with my family during the summer holidays.
Oh, and +1 on Milkman Conspiracy. Although, there aren't really any unmemorable levels in Psychonauts. Milkman conspiracy is my favorite, though. You can probably tell.

Ah I got the name wrong :\
Could have sworn it was the Prophecy.