Horror is best when it feels out of the ordinary... not so much out of place, but when it takes away from normalcy and familiarity. The Shadow Temple in Ocarina of Time was just perfect because it played on mystery previously planted in the game's secrets and dialog. Before we got to the Shadow Temple, we had already become aware of the fact that the Sheikah once dwelled in Kakariko and that something evil was sealed there. We obtained an item that seemed to be of Sheikah creation, and Navi even told us that she could hear the spirits-DEAD PEOPLE, GUYS!- whispering to us, telling us to find it. We even find out that there is a dark history in the past.... So, by the time we reach this horrific place, we're already well aware of its sinister atmosphere. That made actually exploring the temple all the more nerve-wracking.
The same could be said the beneath the Ancient Cistern in Skyward Sword, though many people just weren't scared by it. I can't say I was scared, but the atmosphere was still unsettling because of how much the creepiness factor contrasted with the lighter feel of the game.
If either levels were in a game that focused on darkness for the majority or even entirety of the game, I can't imagine they'd be that great. As it's already been mentioned, you'd become desensitized to it, and it'd eventually feel bland and boring. However, adding one or two more horror levels in a new Zelda game now that technology is better could be great. I can only imagine just how much more terrified I would have been if I could have been the one to hear the demonic whispers inside the tombs instead of Navi just telling me she heard them.