I am looking for add-ons that add new somewhat notable quests or new dungeons without adding any major overhauls or general changes to rest of the game.

e.g. along the lines of Mehrune's Razor or Knights of the Nine or that Orrery quest (and yes I have all the DLC already)

Only major mods I am using in addition the Bethesda addons and DLC are:

All Natural

COBL

Harvest Flora

Unofficial Patches

Skyrim Ingredients

You may find something here of interest: http://wiki.tesnexus.com/index.php/User:Deaths_soul/Recommended_Oblivion_Quest_Mods_List

I'd suggest taking a look at Better Dungeons for existing caves. It'll make them different from each other rather than same old.