Pinnacle Rock from Majora's Mask really scared me as a kid, because by the time I had gotten to it, it was nighttime, which meant a dark place got even darker. All you could really see in the holes were these huge glowing green eyes, and if you got near the hole they'd shoot out at fairly high speed. They were also super huge and made pretty loud noises when they shot out of their holes. I had to have a friend help me clear it back then because I sure as hell couldn't.
I still find the place rather ominous even to this day (even if I don't have any issues with completing it now). Doesn't make me want to traverse the ocean depths, that's for sure.