Oblivion spells were largely the same as Morrowind, minus the really fun ones (teleportation, levitation).
An important difference.
Also Spellmaker was still there, so you could Fortify the Speed of your horse and give it Waterwalking if you wanted to. If that was crippled, then Skyrim's spells is a long-dead Alzheimers patient.
It is crippled. Just being able to make on touch/target version of every single buff doesn't really scratch the surface of the combinatorial potential spellmaker has. If it was just for that you could ditch the spellmaker completely and just provide premade versions of buff spells.
What spellmaker is good for is making multi effect spells where one effect follows another, or spells where you compact several effects you want simultaneously into single casting.
The former is impossible in Oblivion because spells no longer acts like D&D sequencers. It would still be possible in Skyrim as long as it consisted of only two effects (because you would be able to simply equip both spells and cast them in quick succession in desired order (except there are no effects to chain this way in Skyrim, but due to the way attributes were neutered in Oblivion it would be of limited use in Oblivion as well).
The latter is not worthwhile in Oblivion, because of fewer effects that would benefit from that. I guess you could conjure a full suit of bound armor, but apart from that? You can no longer have multiple summons, for example.
Magic in vanilla Skyrim sucked ass in comparison to vanilla Oblivion. Want to cast Night-eye? You have to become a vampire or setrace into a fucking furry. In Oblivion, you just buy the thing or equip a Night-eye-enchanted piece of gear found in random loot. Want to Fortify anything? Hey, you mix potions, right? Well you better, because that's the only way to Fortify anything anymore, and the only thing you can fortify is your own skills.
I actually don't mind less overlap between skills.
You also can fortify others with rally illusion spells, or heal them.
OTOH, you have necromancy that actually uses corpses, wards, no less than 7 different ways of applying each kind of offensive magic via spells and multiple possible casting styles: 2h casting, 2 different spells, spell + shield, spell + 1h weapon, each benefitting different playstyle.
It's not as flexible as Morrowind, but more interesting and arguably more flexible than Oblivion.
Magic is weak in Skyrim, but that's because of badly designed skill benefits and lack of spells.
You can rectify both using mods, like Requiem.
Hell, if Requiem gave you Morrowind style spellmaker it might actually make Skyrim superior in regards to magic system.
But in a very few areas, Oblivion is actually better than Skyrim. Magic being one of them.
Well, no. Oblivion's magic system is abortion of one in Morrowind. Skyrim's magic system is its own thing, inferior and less complex but actually fun and with good ideas of its own.
Does Requiem make magic more "fun" than in vanilla, or is it still about spamming the same one or two things until the enemy lies dead?
There are three things. First, perks are essential; without the respective perks, the spell casting costs would be ridiculously high and the spell effects durations very short. Second, the mod adds plenty of neat new spells, and unlike most other spell package mods, they try to keep the spells TES-themed.
And you get some fun ones IF you develop your casting prowess enough.
By 'fun' I mean stuff like line-of-sight teleportation, ability to grab people from afar with telekinesis or teleporting someone's heart out of their chest and into your inventory.
Standard mark and recall or WMD-grade elemental spells don't even need mentioning.
.
