I haven't played Oblivion in quite a while so I may be out of touch with what's the latest & greatest among the mods released, but here are some I used when I played it:
Ayleid Meteoric Weapons (
http://www.tessource.net/files/file.php?id=5521)
Gives you a little more reason to explore all those Ayleid ruins dotting the landscape. You can find some unique Ayleid-styled weapons hidden away in many of them.
Book jackets (
http://ninthwavedesigns.typepad.com/guilded_lilies/2006/07/oblivion_book_m.html)
Only cosmetic, but does quite a bit for the mood of the game IMO. Gives every book in the game its own unique cover with the name of the book printed on it. Makes those libraries and book shops look a lot prettier. :)
Guide books (
http://www.tessource.net/files/file.php?id=3234)
Adds a series of Guide Books you can buy at the book shop in the imperial city that gives you hints where you can find a lot of the skill trainers, Daedric shrines and other points of interest. Helpful if you have a hard time to remember that sort of thing. :)
Quiet feet (
http://www.tessource.net/files/file.php?id=1999)
If you have an integrated sound card in your computer there is a fair chance your game will slow down considerably when riding a horse or when near four-footed animals due to some weird sound glitch. This mod removes their footstep sound which improved performance dramatically in those instances.
Other than that I'm using a heavily modified version of Francesco's leveled creatures/items mod that tk102 mentioned, the Arachnophobia mod to get rid of the Spider Daedra from the game, Quest Award Leveling.
Most of the other by me considered essential tweaks are handled by a mod I've done myself that adds a new player house and does tons of tweaks to the game, like adding world map markers for all the Doomstones and Wayshrines, increased summon limits, adjusted sneaking and sneak attacks, removing the confirm every time you buy/sell something, a bunch of new spells, items and various bug fixes. And another quick mod that adds a wishing well in one of the Ayleid ruins where you can burn some gold when you have too much of it for a chance of getting... something... depending on your luck. :)
(If you want the ones I've made I think they're still uploaded here (
http://www.algonet.se/~stoffe/obhouse.rar) and here (wishing well) (
http://www.algonet.se/~stoffe/st_well.zip), though I don't remember if that's the latest versions.)