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.