Fuckin' Goldeneye/Perfect Dark. But yeah, I was thinking the same thing.

Oh, yeah. Gotta chime in here, please don't! Of course, I'm one of those people for whom the Wilhelm scream breaks all immersion in a film, I find it incredibly jarring... :/

Something more future sounding would be nice, a la Mass Effect or Stellaris.

1st time I heard it was in Slave Zero.
I remember it very distinctly because the volume was obnoxiously loud on this one sound effect compared to the rest of the game.
Basically every time an enemy mech spawned in from a spawn point, that alert sound was played,

I grind my teeth every time I hear it.

For your list Raf
It's also heard in Street Fighter 4 during the boss battle with Seth,
https://www.youtube.com/watch?v=5FB3Q1PDawg&feature=youtu.be&t=424

That's actually how most alarms work on ships. They are played for a brief period, like a few seconds, then stopped because further playing it serves only as a distraction.

The rest of that song is even better!