It depends on a lot of things:
For some skills, you logically should be able to tell. A few of them (like Disable Device) actually have different outcomes if you fail by a lot vs fail by a little, so you can use that to guess how far off you are. Open Lock lets you tell pretty easily if you succeed or not, since in one case the lock is open.
In the case of open lock and disable device, I've always felt that someone skilled in the art should be able to figure out if a lock is well beyond them or not. If you're used to fixing two stroke lawnmower engines and a Formula 1 car shows up in your driveway, you'll be able to figure out pretty quickly that it's way beyond your skill set. I'll allow a player to ask after the first attempt if they can actually succeed or not. (I do require the first attempt to keep the danger in trap disabling.)
Thematically, these ones can be dealt with by the task being rushed. If you do a roll and fail, maybe the work is just very delicate and you were working too quickly to be reliable. If you can't succeed on a take 10, the task is just plain hard and may take you a coupe of tries to get right. Picking a good lock in real life takes a lot longer than a six second round, after all.
For other skills, there's no way to tell that. Sense Motive vs Bluff is an opposed check, a player shouldn't know who rolled badly or how good the other guys skill is. The only thing they should know is the outcome. The difference between Sense Motive and Disable Device is that I can look at a mechanical system and understand it's complexity. I can't do that while looking at another person who may or may not be lying. This is an extreme one, as you can't really tell if you even succeeded or not (as extreme failure can give you false impressions that you don't know are false).
Listen is another one, as if you don't hear anything, does that mean nothing is there or that their Move Silently is really high?
For these, there's nothing thematically to do. You don't know if you failed at the time it happens. You may not find out until later, or even never. You just take the result you get, and the game carries on from there.
For the opposed and secret checks, some DMs are more secretive than others. They'll roll Sense Motive for the player and only tell the player the outcome.
I don't do that in my game. My players roll it, I roll the opposed check in secret (or a fake roll if there's actually nothing to oppose), and tell them the outcome. That does give the player some meta knowledge of how their dice roll was, which some DMs don't like. I find that my players do okay with that, and they greatly prefer not having me roll their checks in secret.
If you have an action point system, or an ability that allows rerolls (like the Luck Domain), you need to be able to see the dice to know if you should use it or not. If you roll a 2 you might want to use it, but not on a 19 (since if you fail on a 19, the task is likely impossible for you). That's another reason my players roll their own rolls.