edit: omg didn't realize that Rusty Bucket Bay was mentioned like 5 posts above mine. Glad someone else shares my pain!!
Boba Fett in Star Wars: Shadows of the Empire was pretty fucking hard. IG-88 is a close second since it moves so quickly, but its attacks aren't terribly killer.
I also get stuck on Rusty Bucket Bay in Banjo-Kazooie. There's this part where you're deep inside the ship and you have to navigate through spinning fans and cogs. One wrong step and you fall into oblivion. You have to activate two switches to stop the back rotor of the ship from spinning so you can grab the puzzle piece, but it only gives you 60 seconds to make your way back through the fans, up to the surface of the ship, then to the back of the ship, where you have to swim through sludge water (drains your oxygen 2x, and 1x just on the surface) and down into the fan before it starts up again. I pulled it off last night, but after I picked up the jiggy, the fan started up again and I died. Luckily, you keep any items you collect (except music notes, but at least those are saved to your record) when you die. To make things worse, there's this humongous TNT box in a different part of the ship. You have to break it by shooting eggs at it. It will usually break after 4 eggs, but it divides into two smaller boxes. This keeps happening until the boxes are tiny and jumping all around you. You only get max 200 eggs, and I was left at 20 eggs and 2 health by the end. Luckily the next level is Click Clock Woods which is one of the most fun, challenging, and charming levels in gaming history.
Also, as mentioned before, Shadow Temple in OoT can suck it for all I care.