Ecco The Dolphin, Ecco 2, and Ecco Defender of the Future. Easily the most atmospheric games on any Sega machine. On the subject of water related games, Everblue 2, the spiritual predecessor to the Endless Ocean titles could make you feel tense at times.
Legend of Zelda: Majoras Mask gave me a few nightmares, especially because of pretty much anything involving the final night. Everything about the last day, from npc quotes to the sound design, invokes an apocolyptic fear of "oh @!#$ we're all gonna die!"
As a kid, Chrono Trigger has that effect when the future refuses to change. That scream will haunt you forever.
One word: Eversion.