I agree that it felt nice to be able to specialize a character so specific as in Realms of Arkania. It really paints very clear picture of it.
But as you mentioned you will rarely use them and that's what I found kind of frustrating.
Now this is not a problem which comes up in the pen and paper versions of course. Because the Master and the players can just incorporate these. If you go into a pub the players can just start and try to get everyone drunk, even without any incentive to do so. But this is not possible in a crpg. In a crpg each and every event needs to be designed to incorporate different options to "fake" the freedom you have in pen and paper games.
And this is directly related to game length. The bigger the game is, the more options and diversity you can implement. But while Realms of Arkania was very big, still only vers few of the skills and spells were used.
I think another pretty good example is Shadowrun Returns. SR is an extremely small game with just 15 hours or so of game time if I remember right.
Now in SR you had some specific abilities and approaches as well. Like using drones. But it almost felt "forced" if you are heading to an event and already saw "ok, there is the drone shaft, so that I can use the drone approach". Similar to the spikes in Dark Messiah of Might and Magic: You saw spikes, that means that an enemy will come in a few seconds which you can kick into them. So a very constructed design, which had to be done, just to make this playstyle viable.
But there are also examples in SR where it worked even less: If you take the charisma skill there, you can chose different etiquettes at certain points. Now each of these etiquettes like "speak like a scientist" or so, only had a hand full of possibilities to be used in the whole game. As mentioned before - not a problem in a basically limitless Pen and Paper game. But in a CRPG of 15 hours it becomes really hard to make this skill and all these options worth something. And imho the first game pretty much failed there. Especially if you decide to increase this skill half way throgh the game and chose another etiquette. All the possibilities to put it to use might already have passed, so that you chose this skill for nothing.
So I think having a couple of additional of these skills would have worked as well, I think having dozens of them or very limited access to them would either limit the player or force the game to do something "unnatural" like the "drone example" from SR.
Edit: In addition you also have games like Divinity:OS or Wasteland 2. I think these intend to do something which is what you liked JDR13, but they didn't succeed at all.
They had a lot of skills, but if you were experienced within the system and knew what you were doing you had a group which had each and every skill so high, that you always had all options available. But especially wasteland pushed the characters in roles and in addition basically flagged characters as "gimped" if they were outperformed by another one. If you got one character and increased science a couple of levels and then got another one with a higher initial science skill (which can happen in the first 5h or so), you basically know "well, I spent all these points for nothing, now I could as well start over and invest them more clever". While some might like it, the effect it had on myself was that i studied all the characters and before starting the game I already knew which characters I had to recruit at what point in the game to increase which skills. And I personally feel relieved that this isn't necessary at all in PoE.
In Divinity: OS basically the same story, and it even affected combat. It had a pseudo-free character system, which was basically a class system just not based on classes but on attributes. You only had two designed npc characters on release, being a tank and a caster with 2 schools. So what to do? Cover the rest, to have covered all, make a mage with 2 of the remaining 3 schools and either a rogue (which is pretty gimped imho) or a ranger. Now you got everything. And even if you don't have a specific skill like crafting, well you can always just get a hireling to "outsource" that part as I did.
So again I felt forced to fill these roles. But I had the possibility to cover all options at every part of the game.