Spider-Man 2000: The "Monster Ock" final boss stage scared the hell out of me. I remember the sounds it made haunted me every time I played it. I think my dad ended up beating the level for me.
Zelda Windwaker: I remember the Forsaken Fortress stage being quite creepy, hard & confusing when I first played it. Especially trying to get past the pig guards without being noticed. It was fairly tense when they got alarmed.
That's all that comes to mind right now.