I do agree that Oblivion's dungeons often lacked a sense of reward, I think part of the problem had to do with the game's use of level scaling and lack of worthwhile hand-placed loot. Since all the good items were leveled, I never found myself feeling when I found good loot that I had discovered something really great that made exploring that dungeon worthwhile, in part, it was because I knew I would be able to find that in any other dungeon, but even games like Diablo which still feature heavy use of random loot, I would say, still make me feel more rewarded when I find high quality loot than in Oblivion. I think in that case, it helps that these types of games usually have a very large variety of items available, and the really good ones tend to be hard to find, so even though you might get them from random loot, you still feel like it's an accomplishment to find them, because it isn't every day that enemies drop such things. By comparison, in Oblivion, when I find Daedric armor, I just feel like I found another generic item. This is much better now that I'm playing Oblivion with OOO, though, as now the good items are usually hard to find, and tend to require getting past worthy challenges to get them. I'd also say that Fallout 3 and New Vegas handled this better, as did Morrowind. In these games, it helped a lot that they had a fair amount of high quality hand placed loot, in particular, the lack of many non-quest related unique items. Of course I like recieving unique items when I complete quests, these kinds of regards tend to feel more satisfying than just getting gold or something because you can get that anywhere, but not unique items, but in Morrowind, one thing that always made exploring feel satisfying was when I found a unique artifact in a dungeon, and that's something Oblivion never offered without mods. Even when I just found generic but rare items like Daedric armor, though, it still felt good, because such things were hard to find, but considering that Fallout 3 already handled this better than Oblivion, I would say, I think that Bethesda has probably learned the lesson and we can expect a better approach in Skyrim, I hope I won't be dissappointed.
Of course, not every dungeon should something really impressive in it, because while I sometimes get a sense of dissappointment when I explore a dungeon and come out with nothing worthwhile inside, I'd rather deal with the occassional dissappointment than have the satisfaction of finding good loot ruined by making it too easy to find it. As a general rule, I'd say, the best loot should be find in dungeons that are appropriately dangerous. In other words, I don't expect to find the Epic Amulet of Ultimate Doom in a cave populated by a few low level bandits using fur armor and rusty weapons, on the other hand, I don't want to fight through a dungeon full of high level Daedra and deadly traps, find a chest with a very hard lock at the end, and open it only to find it's full of calipers.
Often the most rewarding loot found in a dungeon is in the form of the armor or weapon an enemy is carrying, and I like that. It's unrealistic to expect treasure just sitting there at the end of every dungeon.
I'd say it depends on the type of dungeon and the type of enemy that populates it, if I go through a cave full of bandits, I do agree that it's going to seem kind of odd if they're using iron weapons and leather armor when they have much better equipment sitting around in chests, but not all dungeons are populated by enemies that can use weapons. Take Morrowind's Dwemer ruins for example, the enemies in those were usually Dwemer centurions, and those couldn't use weapons, in this case, of course if there's good items laying around, they're not going to use them. Of course, the most satisfying loot in those places tended to be Dwemer artifacts, which makes sense, although they weren't as good as you might think when people talk about how impressive and valuable they're supposed to be, being as at best, Dwemer weapons and armor were only mid range items, and the non-weapon artifacts were somehow actually not all that much more expensive than silverware items in the game, despite all the talk of how much collectors will pay for them, and usually I didn't bother collecting them except for Dwemer coins, and one of each item so I can have my own collection.