One that hasn't been mentioned yet?
The last mission in Metal Slug 3. I went in for the first time not knowing what to expect, beyond probably just a run to the boss, epic fight, yadda yadda yadda.
What I found was a level that took just over half an hour for me to beat, and one of the most epic I've ever played. Every new twist in the level had me saying "Holy shit!" every few minutes.
I won't ruin it beyond that, I suggest you try and play the game :D

Half-Life's "Blast Pit" (singleplayer) and Quake II's "The Edge" (multiplayer).
My favourite moment gaming moment is in Blast Pit, there's an elevator ride in front of a giant concrete cylinder and there's radioactive stuff below. First time I played I was overwhelmed by the sheer size of it, it made me realize what a giant thing Black Mesa was and what a big accident just happened. The bright green light coming from the radioactive residue added to the feeling.
The Edge is probably a lesson in map design.
Honorable mentions:
Delta Force's "Ice Flows"
Carmageddon's "Beef Curtains" (best game ever, I want it on GOG now!!!)
NFS Porsche Unleashed's "Schwartzwald"
Quake I's "DM6" (dear God, I remember playing that map all night long)
HOMM III random maps (XL, with underground, monsters: strong, difficulty: impossible, water: none)

Carmageddon 2, Unfair Funfair map.
Clowns, elephants, giant penguins, junk cars, hay bails, great jumps, loop-de-loop's, water log ride, and a roller coaster.
What more could you ask for from any game?

That's exactly the same experience I had; for me it was the combination of the music and Holotapes about what that scientist was doing. I was only 13 at the time and I almost pooped myself; sufficed to say now I still think it has an awesome impact as a level.

Agree with quite a few, but I shalln't bore anyone with those.
How about Prince of Persia: Sands of Time, when you get the final sword. Suddenly you're running around oneshotting enemies you've been before with epic music in the background (Tower of Dawn). Was a nice feeling!

Baiton Kaitos:
Garden of Illusions on Mira. Most funny level/dungeon ever...

First level of Unreal.Great atmosfere and this screams of prisoners behind this door...really creapy.

Already mentioned:
Pscyhonauts: Black Velvetopia, Milkman Conspiracy, Lungfishopolis (especially the first part underwater, loved that!)
Unreal: Vortex Rikers and NyLeve's Falls (the first two levels)
Jedi Knight 1: The Falling Ship
My contributions?
Call of Duty 1: Stalingrad (gimme a f*&*#^$ gun!)
No One Lives Forever: Unexpected Turbulence
No One Lives Forever 2: The Getaway (best "high"-speed chase)
Thief 1: The Lost City (very atmospheric)
LOTR Return of the King: Pelennor Fields (it must have been the music)
Cheers!

I loved the Chaos Theory co-op stages that you could play split screen and were tied into the main story how cool was that!

Xen in Half Life 1.
Football field level in Duke3D (such a simple level; perfect for a boss fight! Blowing up the blimp for extra goodies was awesome, too!)
Pillar of Skulls in PST
The circus level in Blood :D Loved those minigames involving severed heads
In Jazz Jackrabbit 2, those two 70's levels really stick in my mind :p Hitting those smoke rings from the caterpillars and getting your keys flipping around as well as messed up sound and video was really...weird
Dead Simple in Doom 2 is definitely my most favourite level in that game.
Astro chicken in SQ3 :p heh heh good times
The "sword" duels in Monkey Island, those were hilarious!
Oh ho last one on my mind; The crazy part in Serious Sam: Second Encounter, the tube level that is constantly spinning spinning, that was friggin awesome. Ice level was sweet, too. Man that whole game was sweet, actually.

Seconded! Seriously as far as the best levels go in the strict sense of the word it is far from perfect but the combination of level design and sound/ambience direction is just stellar!

"Feint! Parry! Riposte!" in Freespace 2. That's my favorite out of a plethora of excellent level design in that game.
While TIE fighter and the other Lucasarts games kept resorting to your commanders making retarded decisions to ramp up the tension (we're loading all the heavy weapons in our assault of a Calamari Cruiser into this Storm Trooper transport) or (we're low on ships, go dogfight some rebel ships in a TIE bomber) Freespce 2 just had superb level design the whole way through.
F!P!R! is my favorite for so many reasons, all that buildup of learning about the colossus, arming it, now you want to take the GTVA's toy out for a spin, setting up the ambush against the enemy fleet's flagship, you're flying the Perseus, my personal favorite ship, oh such a great level. Here's a youtube clip, unfortunately the guy's using the "realistic lighting" tag, so that's why everything's so dark:
http://www.youtube.com/watch?v=ox-LiMWetns

Seriously? The whole of Xen is the worst part of HL1 for me, not least for the jumpy bits. Completely out of place, and was a poor end to an otherwise excellent game.

Ultima 9: ok, compared to the previous titles and its bugs made it pretty sucky; whatever. Anyway, i experienced one of the prettiest colors of a sunset ever there. Looking from the top of the castle down the land into the sunset.
Quake: dm4; don't remember how many lives I spent in there. Controlling the level from a single point with the pineapples ;)
Gothic 2: i found the way the land was architectured very believable. From the wizards tower, the waterfall with its dungeon thru the rocks to the inner area, the city and the mountain paths leading to the farm. It was just incredible.