Vanilla Oblivion is massively overhyped. Modded Oblivion is still one of the best games available today.
I HIGHLY suggest trying the vanilla game first (no mods). Play until you get bored and get a general feel for things. Then, install the following mods and try again. Please note that I have not tested all of these mods myself, but have heard good things about all of them.
Obscuro's Oblivion Overahaul 1.33. It changes a LOT of aspects of the game - for instance, you no longer start with the option to fast travel to all different towns, and monsters no longer level up with you; some monsters start out weak, some start out very strong. It generally makes the game much harder but far more rewarding. Most consider this one a must.
Oblivion XP. Does away with the vanilla leveling system (using skills to eventually level up and increase certain attributes +1 to +5 points based on which skills were used) and adds an EXP-based leveling system like most RPGs.
Qarl's Texture Pack. Higher resolution textures for many areas of the game. Generally makes things look prettier.
DarkUI. Replaces the bright tan UI with a darker red color. Much easier on the eyes after crawling through a dark dungeon and having to stop to open your inventory.
Toggleable Quantity Prompt. Tired of the "How Many?" box that pops up each time you sell something? This allows you to skip that by holding Ctrl (sell 1) or holding Shift (sell entire stack) when selling something.
Quest Award Leveler. If you get a quest item very early on, the stats will be balanced for your low level. With this, your quest reward items' stats level up with you, so you don't have to save certain quests until much later in the game just to get better item stats.
Alternate Start. This mod skips the beginning tutorial (very boring) and allows you to start the game in several different ways, each with a different background.
The Lost Spires. Free quest content; what more needs to be said?
Deadly Reflexes. Adds combat movies and some physics based combat stuff. Pretty cool and pretty gory.
There are TONS of other mods, many of which enhance graphics, spells, items, sounds, etc. I highly suggest installing a few of the ones listed above and giving it a whirl.