For me the beauty of mods is that they allow the player to craft their own game. In effect, you become a kind of junior game developer in your own right. With mods, Oblivion is never quite the same from one game to the next. The Oblivion I played in 2006 is not the same Oblivion I played in 2008 and the Oblivion I played in 2008 is not the same Oblivion I play in 2010. It is always changing, always fresh, always full of surprises, thanks to mods.
It all depends what you want out of the game. Some players want to be guided, other players want to guide themselves; some want to be told a story, others want to tell their own stories; some want the "game experience as the developers intended it," others want to make their own game.
More importantly, mods are critical to my roleplaying. I would have a very hard time roleplaying some of my characters without the aid of mods.
One of my more recent characters is a race, Dea Obscura, that, because of their hideous appearance, is hated and feared in Cyrodiil. They are sold on the black market to the Arena. This character stays permanently inside the Arena until she wins her freedom by becoming Champion. I use about ten mods to make this character and her story come alive for me.
She starts life inside the Bloodworks. Because I use an overhaul mod becoming Champion cannot be accomplished at level 1. A new chaindoll and a mod making chaindolls useable allows her to train her weapon skill between fights. A Blacksmith in the Bloodworks will repair her raiment and train her armor skill between fights. Yellow Team gear is lootable, so she can afford to pay training and repair bills.
I added slave quarters off the Bloodworks. I also added a second Dea Obscura NPC to the Bloodworks for atmosphere. This slave sleeps in the new slave quarters and trains at the new chain doll in the Bloodworks the rest of the time. She does not leave the Arena.
It takes me about 15 to 20 levels to make it to Champion. At that point my character is free and can leave the Arena for the first time. After that, My character and I play a variety of quest mods for the remainder of that character's life. This is just one roleplaying experience that's possible with mods.
As I said once in another thread, Oblivion is one of my favorite games; modded Oblivion
is my favorite game.