The problem with dialog skills is that the way they are implemented involves 0 gameplay. But trying to make mini-games based around dialog skills is ultimately a bad idea. Playing a mini-game, completely separated from the context of the rest of the game is hardly much more fun. First, mini-games tend to be simple and isolated from the rest of the game. So while you technically have gameplay now, it is as if you mixed a completely different game for no reason. Second, these mini-games usually go against the idea of what an RPG should be, by either abstracting too much away from what they represent or outright having nothing to do with it in first place.
I think the term "mini-game" gives people the wrong idea. Done right, the introduction of dialogue mechanics needn't feel any more like a jarring minigame than, say, shifting into turn-based combat does. I think a key focus would be on how to make it so that the dialogue mechanics feel constantly present and relevant in the world. Combat remains relevant even when it's not taking place because players are looking for weapons and armour, collecting spell scrolls, looking for potions that could give them an edge, etc. There must be a way to involve dialogue in the same way, make it so that players are constantly on the lookout for ways to give themselves advantages in the dialogue "mini-game" during overworld exploration.
Going back to Oblivion again (mostly just because it's the funniest example), the issue is that, as you mention, it feels detached from the rest of the game; the speech pie chart appears for no reason and players generally don't understand why they're having to play this odd thing that's come out of nowhere. I don't think it's an inevitability that new, non-combat game mechanics will end up like that, though - Oblivion's error (in addition to the minigame being shit) is that the
only thing that affects it is your Personality/Speechcraft skill, and there's not really anything else you can find or do in the world to affect the speech game.
You the player irl have whatever innate irl capability you have, of choosing persuasive lines of argument from dialogue options, from background knowledge, lore, etc., so in theory there's no need for a separate "skill." But yet in an RPG you are supposed to be playing a character who has x amount of "persuasiveness" or speech skill or disposition or whatever, in the abstract, which may be notionally higher or lower than your irl skill.
Exactly, the approach used to speech right now doesn't feel like you're playing a persuasive character, it just feels like you're choosing the Speech option (whether outright tagged or just obviously-telegraphed). A degree of abstraction (such as a card game) gets around this by leaving the exact specifics of your negotiation up to the player's imagination, but lets you actually
feel your character's skill at negotiation, as the more persuasive your character is, the more advantages you get in the dialogue game. That's part of the core of any RPG, IMO - being able to tangibly feel your character getting more skilled and able.
People have mixed opinions but I like the lockpicking minigame from Fo3/NV for that reason. You don't feel your character getting better at lockpicking in Fo1/2, you just have to reroll less while clicking on doors. But in 3/NV you can actually feel locks becoming looser and easier to pick as your character becomes a more skilled thief. The only real problem with the lockpicking in those games is that it freezes time; if it weren't for that then they'd be really good. It can be argued that they still rely a bit too much on player skill, but they've got the right idea, I think.