Getting the orb on top of the Tule Tree in Guacamelee! was very intense.
Beating The Radiance in Hollow Knight, I got so nervous making it to the last phase.

Also, how could I forget, Alien fucking Isolation.

Brilliant game but holy shit, creeping through the corridors of the space station knowing that the xeno was out there and could show up at any time and one-hit-kill you was terrifying, even more so when it was actively roaming the map and you had to try to stay out of its way and progress without being spotted and slaughtered. The old school save system and the fact you couldn't kill or even outrun it just made it even more harrowing.

Walking in Cynthia's house for the first time in Pokemon Black/White. Nothing beats that tense feeling.

Honestly, the only thing I remember is the Submerged Castle in Pikmin 2. The Waterwraith appears after 5 minutes in each subfloor and you're powerless against it, leaving you with the necessity of escaping and avoiding his attacks as quickly as possible. Now that I'm good at the game, I never come across with it, but it was a huge deal back when I was a child.

Ya know, I will also say The Nursery from ZombiU (Such a hidden gem and underrated game btw)
Gears of War 1 on insane when your in those mine carts, very intense moment, died so many times.
Most bosses from the Souls games when it was my very first playthrough
Im sure there are lots of others, just cant think of them right now

The final battle in Fire Emblem Fates Conquest.

Probably that nursery level in zombiu for me too. Also some of the extra levels in Donkey Kong Counrty Returns tropical freeze.

When I first encountered Hunters on the silent cartographer in halo combat evolved. That was intense!

Yes, the top for me too.
There are other games with tense or horror levels, but while they can have more explicit horror level, they can't reach the Cradle for tension.
The Bonehoard in Thief 1 is scary, but less tense, you see immediately the dangers, and you can find some relatively safe places too.
The Halls of the Blind in Diablo 1 are scary and tense, but they are too small and the quest is quick and not too hard, darkness apart.
Scary levels abound in Tomb Raider 2, but they are more adrenalinic and almost splatter, but far less tense.

Sword Saint in Sekiro is the one that comes to mind right now. Honestly a lot of those moments in Soulsborne-games

All 15 hours of this