I havent delved into Oblivion in a year + probably, I know a few of these mods have had major tweaks since I have last used.
First one being the Unofficial Oblivion Patch, reportedly fixes several thousands bugs Bethesda never did, alot of seeing through land issues, I had in a few spots. I have never really experienced 99% of the reported ones here besides seeing through land and wall's, which this definately fixed for me, there is nothing majorly gaming changing here, just bug fixes, still absolutely recommend it, If your playing through the expansion's there are unofficial patches for those as well on top of this I beleive?
Oscuro's Obivion Overhaul - My favorite mod, I dont like some of the newer changes, but it stops the leveling up with you system which I dont like, if I encounter a Vampire at level 1 it should stomp my *** and every bandit should not be running around in Diamond armor, it makes AI much much better, spells little easier to cast from what i read know a days, it overhauls a good majority of the game, as a new comer to the game, I would definately avoid this one for a while, as it changes alot and I have no idea what it changes anymore.
AF Levelling mod - I think this was the levelling mod I use to use, in the current system, I dont like the fact you gotta keep using the same skills over and over again to level those up, AF was alot looser with that, and made it much easier to just focus on playing and not worrying about making what you wanted to make. It just made leveling so much more friendly, I felt like previous I had to grind to raise the skills i wanted to raise, this did away with that.
Unique Landscapes is a fun one, makes some very very pretty and unique area's, there is alot of fun texture packs and AI packs, and some really cool combat ones, such as Deadly Reflex, which apparently is up to version 6, last one i played was 3, but this adds like different combat moves, ability to stun monsters and other special attacks.
All of these can be found at
www.tesnexus.com, there is soo many mods for this game and some of them, like Oscuro's literally completely overhauls the game, these were the main ones I remeber and still have direcoteries off, I know i played with quite a few over the years, but these were the ones that stuck out the most, AF and Oscuro's.