Metroid Prime. The game oozes freakiness, it especially shocked me when going into the labs in the ice zone. Metroid III was quite freaky too, but mostly because you feel so claustrophobic in there.
Maniac Mansion. I thought I was the only one scared of this game. When I first went into the Kitchen too soon and found Edna (or was it the young one?) routing in the fridge.
Zelda: Ocarina of Time. It was the first 3D adventure game I had played which probably added to the fear effect, but the Forest Temple dungeon as fully grown Link was freaky, as well as exploring the King's Tomb and Ganon's Hyrule City. Who new zombies could be scary. Amazing game that despite the camp elements.