There's a few ginormous threads here if you search, there are literally thousands of mods available. There are three basic types though, and if you haven't played before I'd only use the first group and a few of the second -
1. Graphics & environment mods - texture upgrades, weather packages, city remodelling. Go nuts with these, they make the game look amazing.
2. Annoyance removers - decent UI packages, no psychic guards (so you can actually be a thief properly), shop changes (so each shopkeeper doesn't have only a limited amount of gold to buy your stuff), that kind of thing.
3. Complete overhauls - to change the fighting system, the way leveling works, the magic system etc. Play at least a few hours of vanilla oblivion before even thinking of installing any of these.