Well here are some I felt are really good
Mansion of Madness, Timesplitters Future Perfect (really nice mansion, but its flaws include being linear, some rooms inaccessible and too bright in most parts. well TS is a comedy/parody, so the mansion still is really good considering the theme.)
Himuro Mansion, Fatal Frame (a Japanese type of mansion, still very dark, very scary and becomes more explorable as progression is made.)
Barrows Mansion, Clock Tower (even for its time, it is one of the best designed mansions with great exploration and interaction with mansion's enviornment and very dark atmosphere as one tries to avoid a seemingly unstoppable horror.)
Spencer Mansion , Resident Evil (yes one of the more commonly known mansions. The original was actually better than the remake because it allowed more free exploration in the house from the start, the remake locked things up in such a way that the beginning felt too linear before obtaining the items to access more rooms.)
Edison Mansion , Maniac Mansion (perhaps one of the first videogame mansions that was huge, free to explore (until a tentacle gets in your way), and pretty interactive (how many mansions let you turn on the sink or take food and batteries out of a refrigerator? or put a hamster in a microwave?). And despite its bright and upbeat nature, there was scary feeling in alerting one of the people that chase and capture anyone they see.)
Abandoned House, Forbidden Siren (although not a mansion, it still kind of counts because it presented a chilling flavor that combines exploration, stealth, and fear towards the few yet very dangerous zombies present. One could spent as much time carefully trying to find safe way to escape as much as the time one could explore an actual mansion! )
I throw in my vote with Lenyomanic. As a child I was terrified of Boo's Mansion and went out of my way to avoid the level. Up to that point, every level was vibrant with plenty of space to sprint and platform. You couldn't hold right on the directional pad and breeze your way past simple conflicts in Boo's Mansion--you had to solve puzzles and you'd find yourself back-tracking *alot*. The inside was filled with irritating ghosts side-scrolling across the screen in various patterns or rotating in a circle or being general nuisances. And, god, the Boo's that would follow you were the source of my disdain. Especially seeing as their sudden appearance could block you from taking a stairway or doing something to defeat the level.
And if King Boo was crowding the floor, then Mario's vertical jump couldn't hope to elude it. Guh.
Though my strongest vote goes toward the Raccoon City Mansion. That place still gives me the willies.