This post is old, but… I figure I should publish it rather than intend to edit it more…
After getting a new hard drive and video card, the first game I reinstalled was Oblivion. I wanted to check out all the cool fanmade mods that’ve been made over the last 2 years since I first played. To the left is a screenshot of the mods I’m running.
Basically, running the official mods plus the Knights of the Nine and Shivering Isles expansions, Francesco’s overhaul mod, a couple of mods that make NPCs look better, the Natural Weather series of mods (plus a bunch of texture packs which aren’t listed to make the game look even more awesome), and some quest-type mods. The most amazing one is the Tales of the Fiend quest mod. I definitely recommend it.
You can find it and other mods in several places. First, TES Source and Planet Elder Scrolls are great sites with ratings and such, but I tend to look for links off of those sites to sites run by specific modders to read up on the latest happenings and download the latest versions. For good lists of recommended mods check out TOQL and UESP’s must have mods.