Needless to say, spoilers down below.
In my opinion, I think Oblivion's main quest is the weakest of the big three ES games. Morrowind's was fantastic (lore-wise at least). Skyrim's was good enough to finish and feel satisfactory. Oblivion's...I honestly have to force myself to finish it.
- A Daedric Invision that never really happened (I know about Enhanced Daedric Invasion, but it's seriously outdated and has a reputation of being buggy and unstable). City Counts think the Oblivion Gates are a big deal when nothing happens to their cities. You'd sooner see a cliffracer flying over Chorrol than see a Daedra attack a city.
- A weak antagonist (Mankar Camoran, not Mehrunes Dagon), both gameplay-wise and lore-wise. I didn't even realize he was the "final boss", per se, when I fought him for the first ever in Paradise. I thought he was just an elf trying to protect his master, Mehrunes Dagon, who I assumed was the end boss.
- You don't feel special/unqique. The Nerevarine did something only he could do. The Dragonborn did something only he could do. The Champion of Cyrodiil did something anybody with talent could do. It's a worthy feat, yes, but nothing special.
- You're the 7th Champion of Cyrodiil.
- Most mission structures during the main quest were stale and repetitive. Besides retaking Kvatch (which I should note is also poorly done), most of the beginning of the main quest was running back and forth doing asnine tasks like waiting for an Argonian to decipher a book, following a guy through a sewer while he walks slow as molasses, etc. After you give Martin the Mysterium Xarxes, he'll ask you to go on four boring fetch quests. Bethesda attempted to spice this up by saying "Nobody has ever went there and returned alive", which honestly doesn't help much. However, I'll admit, the Battle of Bruma was pretty cool.
- I'd rather watch glue dry than try to close Oblivion Gates. What's the point anyway? It's not like leaving it open will have repercussions since the Daedric Invasion is a joke. Plus, what's my incentive? Bethesda expects me to go through a lackluster version of "hell" for, what, a Sigil Stone? Please.
The Knights of the Nine DLC + Knights of the Nine: Revelations (mod) is a more worthy endeavor than the original main quest. It kind of pains me to rant about how bad Oblivion's main quest is since I love the game so much, but I can't be biased.
Tl;dr: Daedric Invasion svcked, main antagonist svcked, you're not unique, most mission structures were repetitive and lacked any sort of imagination, closing Oblivion Gates were worthless.
Well, that's my two cents. Opinions?