Bio/Poison is useful in several Final Fantasy games. It works against some bosses. Toad, Stop, Confuse, and Death are all useful in FFV when fighting strong enemies and using a party with mages and weak fighters. Overall, status spells are typically good when fighting strong random enemies, but many games now try to keep enemies close to your level , making them pointless.
Total incapacitation of enemies like Bad Breath (when lucky) or Pain can be quite fun.
Attack magic is woefully underpowered in both Xenogears and Final Fantasy II to the point that it is only useful in a few required spots.
The AGWs of Xenosaga were only marginally useful.
Most of the shields in Megaman games. Leaf Shield was great, but the others disappeared too easily.
The fruit in Pac Man was pretty useless as all it did was give you points, it didn't help you at all and it didn't even fit in with the story.