you can fit surprisingly much into a small space with a bit of optimization and clever drawing.
for instance, here's the tileset of my game's protagonist with 20 frames of 3x3 tile sprite, it's quite enough for a relatively smoothly animated actions.
and here's the same thing crammed into a tileset.
that said, there's multiple more or less advanced ways to get more space for the player sprite.
for instance, as you can see, i have less than one row of tiles free on this tileset, and no alternate weapon sprites etc.
one could use object weapons, or just some single-tile alterations to the sprite to get like, a gun or something, but in my game for example, there will be atleast 3 different ones,
with relatively large changes to the attack frames, so we're just going the other way and reloading the sprites based on what weapon is in use at the time.
another way would be to use chr ram for this, you can load up to 4 sheets of sprites into the 4 ram banks, but especially with tile animations, that will get complicated really fast.