Hybrid Heaven gets my vote! Most will understandably say Ogre Battle 64, but Hybrid Heaven is a short and unique RPG-esque game that definitely deserves some more love. You solve puzzles by platforming and shooting whilst exploring a secret base to uncover the absolutely batshit plot that I won't dare spoil (I went in totally blind and it was way wilder that way). Combat is a sort of turn based but the battle plays out in a very cinematic way. You choose how you attack using weapons or just straight up melee combat, focusing on various limbs of enemies to achieve different results. It's fun, its only 12-15 hours long and features some decent replayability.
Yeah, third party developers really did the system dirty once the PlayStation hit the scene. I heard that it was common for a game to be announced, then immediately cancelled -- Mother 64, Final Fantasy 64, Shadowgate 2...
I think the N64's limited storage space has far more to do with it than the PS1's success. The Saturn managed to get plenty of RPGs despite the PS1's success and the Saturn "dying" a couple years sooner than the N64 did. Sprawling RPGs just weren't a great type of game to try and make on a system that more or less forced 3D whilst having more limited storage space. I'm sure there's more to it than that though.