There certainly wasn’t any excuse to keep using them after Mario 64 came out and showed how 3d games should be controlled. Spyro, Soul Reaver, Banjo Kazooie, Ape Escape, Medievil, Zelda – the whole industry moved into that direction in the 90s,
Not really, and those controls were abandoned too for PC controls.
Tank controls had less problems with the game design, as much as people like to look back and act like that wasn't the case at the time. It was easier to have your character with the camera following it to show off the level then it was to have to fight the camera. You mentioned Spyro and outside of collecting it doesn't control like Mario or Banjo, because they were trying to aim for the middle. Turns out all of that would be thrown out for direct camera control by way of two analog sticks which was already an option for controlling 3D games at the same time the games you listed were relevant, but just on computers instead of consoles.
Tank controls were the least experimental and the most balanced until the dual stick movement and camera controls took over. All the prototype stuff was fine for the time on consoles, but that's just it, it was fine
for the time. Eventually the industry moved forward prompted by PC developers who finally unified with the console games through the Xbox console which PS2 also quickly joined in on. There were some stray TR/RE and Banjo controls scattered that gen elsewhere but then faded away.
Personally I would rather not go back to either of those, or any of the other incredibly chunky inconvenient prototype controls from the 90s consoles. There's nostalgia there but it's kind of like going back to use a pager instead of an early smartphone. Why?