I just cleaned up my old bookmarks today and found some great lists.
TheNiceOne's mods:
http://www.gamesas.com/topic/1037276-theniceones-nice-to-have-mods/
Stealth related mods:
http://www.gamesas.com/topic/1093197-running4covers-list-of-stealth-mods-2nd-thread/
Environment mods:
http://www.gamesas.com/topic/960829-environment-mod-list/
Magic overhauls:
http://www.gamesas.com/topic/814477-magic-overhaul-comparison/
Textures:
http://devnull.sweetdanger.net/obliviontextureoverhaul.html
Probably my favourite quest mod:
http://oblivion.nexusmods.com/mods/12997
Everybody should try Unique Landscapes at some point
http://oblivion.nexusmods.com/mods/19370
How to install the above.
http://tescosi.com/wiki/Main_Page
Have fun and start small until you are comfortable with building a stable modded game!