The Submerged Castle cave from Pikmin 2, with the Waterwraith as the protagonist. The level design and music are different from the rest of the game. It was really frightening as a child, and today is still intense and a bit spooky.

The Shadow temple from Ocarina of time. I've always hated that fucking temple.

F.E.A.R., sort of. It's a horror game, but for the most part it has a clear distinction between action and horror parts. The action parts aren't scary at all, and the horror parts don't have any action. F.E.A.R. isn't probably very much in the spirit of this thread, but this thread brought up F.E.A.R. in my mind as soon as I saw it.

All bioshock...

Max Payne. Particulary Max's playable nightmares, which were a sudden change of pace and some horrific experience altogether.

Some Vaults in both Fallout 3 and New Vegas could prove to be tense and borderline scary places. I definitively remember them that way.

There were some sequences in Final Fantasy VII that scared me as a kid. Like when Barret peeks into Jenova's machine and you're greeted with a short CGI of some monstruosity. Or whenever that chillin' ost started sounding and you knew something creepy was going to happen.

Could probably think of more examples later.

Bioshock counts as a non horror game right?

Those were going to be my nominations. The vaults in Fallout can be damn freaky. Also, in FF7 when you wake up in the cell in the Shinra building and there's blood everywhere scared me a bit.

The Scarecrow hallucinations in Batman: Arkham Asylum. Especially the first time the game surprises you with it.

Mother 3 tanetane island. Still creeps the s*** outta me. In fact Itoi had to tone down the original dialogue.

Soul Reaver, and Legacy of Kain games... They're action adventure, but with a very dark touch...