I agree that the general thievery system from Oblivion could use some work. I'm currently working through Two Worlds II, and although the game as a whole pales in comparison to ES games, they actually did a good job with the thieving aspect. You can pickpocket people, but you have a limited time, and it involves a pretty difficult (but well done) minigame.
The best part though, is the way they handle break ins. Breaking into a house is a matter of watching the guard and citizen patrols and picking the right time to pick the door lock. The lockpicking minigame is easily the best I have played as well. It is fun, challenging, and involves a decent bit of skill and dexterity (you will break many lockpicks once you get to master locks). Also, time doesn't freeze when you are picking locks, so it adds some tension. Once you are inside the house, there are usually quite a few locked chests/cabinets, and once you get past the early stages of the game, most of the containers have really good loot as well.
The only negative I have found, is that there is really no consequence for doing these things. If you are caught trying to break into a house, you will just get the stink eye and/or a lecture from a guard or citizen, and then are free to go on your way. If you break into a house that has people inside, they will just look at you funny and maybe comment about how the stuff you are attempting to steal doesn't belong to you. Sometimes they will yell for the guards, but you usually you have a while to leave before they show up. Another benefit is that the shop owners don't magically know that what you are selling is stolen. Overall, I think a similar system would work well in Skyrim, as long as they increase the consequences a bit, and maybe add a chance of having your looted equipment detected and re-possessed.