First of all, simply saying that there's no point in leveling up since your enemies level up with you is plain stupid (sorry guys). In a linear game, as you level up you encounter more and more challenging enemies so the difficulty stays (in theory) the same or increases.
Oblivion (and Fallout 3) are open world games and the player dictates in which order to attempt the content. Granted, there could have been no quest/mob leveling and they could just force the player to reach a certain level before he can stand a chance at some quests and be able to roll right over other quests that he's outleveled. Does that sound fun to you? Having the majority of quests impossibly hard or boringly easy?
HOWEVER while I defend enemy scaling in principle when it comes to open world games, the implementation as seen in Oblivion could use a lot of work. So please, read on before you go off to crucify me for the first two paragraphs
First of all, there should be some caps on certain enemies. Having highway bandits in full Deadric armour around every corner is ridiculous. Also, having my lvl 40 one-man-army realistically challenged by a Wolf is also a bit too much. Low level quests should also be capped at certain points (early guild quests for instance). This would make leveling up feel more meaningful since your character would become a force that not everyone can stand up to.
On the other hand, some quests should have a minimum difficulty. For instance, final guild quests should be about lvl 15-20 minimum and not scale bellow that. You can attempt them before that point, but expect to be outleveled and summarily destroyed. Again, some enemies should have a minimum level bellow which they are not scaled. This would provide the game world with places that are accessible only as you get stronger.
Furthermore, the player should be able to stay ahead of the leveling curve by smart building. In linear games the player is rewarded for good choices in character development by being able to beat content with less trouble. Oblivion only marginally allows this, but this is more a consequence of the terrible character development system, but that's a matter for another time...