Games that didn't use a battery to retain their save data should work pretty much forever, as well as any games that didn't really retain any save data.
The simple fact that there really aren't moving parts in a Super Nintendo helps a lot in regard to durability, since the cartridge itself is effectively a 'companion' PCB inside the plastic shell that links up to the SNES system's logic board to have the necessary boot loader for the given game you plug in.
If I remember right, many of the Nintendo cartridges and even some of the flash carts that enmulate the function of the backup save use a watch-style button cell battery, which can be had pretty cheap. Also, I would think if you were enterprising enough (and skilled enough with a soldering gun), you could theoretically replace the button cell battery with a rechargeable, if you knew there was a way to give it enough current to keep its battery charged.
I can't speak for the SNES cartridges, but I know this was mentioned as a solution for the Dreamcast's internal battery (that acted as a CMOS).