Ovlivion

There are a few things needed to change the way leveling sucks in Oblivion that are extremely important to me.

Oscuro's Oblivion Overhaul (OOO) makes the game world a lot more interesting and the dungeons static. The dungeons won't level with you making you actually feel weaker as you level. It's hard to start out because you're a weakling, but it's also very satisfying to have enemies make your crap your pants only to slowly be able to take them out and then eventually be strong enough to massacre them. The feeling of progression makes the game so much more worthwhile and the dungeon crawling is much more rewarding as well as there's actually a point in going to random dungeons that will actually have variety. OOO also adds several dozen other things.

There's a mod that lets you add +5 at every level up as long as you used that skill. In the vanilla game you have to hold off on leveling up (sleeping) so you can grind in an area to make sure you get +5 so the level isn't wasted by a +1 or +2 in some stat. This makes that not be the case and you can just pick the area you want to grow in. It also makes OOO slightly easier (which it needs if you're playing on a reasonable difficulty). Some may view this as cheating, but I think it really just makes the game's leveling system punish you less which results in more fun.

There's also an upcapping mod that allows your skills and attributes (I think) to go up to around 200. If you're playing a long drawn out game with OOO, especially if you're focusing on a specific type of character, you will eventually hit the cap and things will start to stagnate. The game gets less fun when you feel yourself not getting better, so setting the limit higher makes the game thoroughly more enjoyable.