This could be a spell that encapsulates the series' magic system, or just a notable standout that stuck with you, or whatever.
Favorite - Giga Slave from The Slayers. It's a spell the main character cobbled together from an ultimate spell invoking a demon god, instead changing the incantation to instead invoke an entity greater than that. It 100% destroys its target if it connects, but requires a level of control and focus or it would destroy the world outright. It's cool to me in that it was an invented spell and you don't often get to see active creativity in a magic system. It also creates a cool moral question for the characters about using such a spell. The magic system of Slayers is just really cool like that.
Least Favorite - Avada Kedavra from Harry Potter. It's an instant death spell and anyone hit by it dies instantly with no chance of resuscitation or revival. It's illegal in the setting, but it's only ever used by the bad guys. There's no history to the spell, no counter measure, and it's considered just evil. Meanwhile there are other spells that are technically lethal on hit, but are perfectly fine to use against an opponent during mortal combat. It's interesting because on the surface, it invites moral questions. A quick and painless death spell that has little chance for collateral damage if it connects.
I'm a sucker for shit that makes it impossible for people to lie (e.g., prevalent in the wheel of time). I think it sets up for interesting relationships.
Least favorite are time travel spells, pretty much for similar but opposite reasons. I think time travel spells ruin immersion.
Time travel ruins any narrative that isn't carefully set up from the very beginning to support it. Chronological cause-and-effect is the foundation of story telling. What are the stakes if the characters can simply rewind?
I think truth magic is an interesting concept, but I feel it's a hard thing to implement into a story in a way that wouldn't dissolve plot tension. At least with high drama and personal angst prevalent in a lot of fantasy. I think it would introduce interesting dynamics though. What do you enjoy seeing when truth spells come out?
I agree with the time travel spells. I think even small ones (reversal of entropy, or rewinding) spells introduce complications. Like if you reverse time to bring somebody back to life (really dangerous application of magic for plot purposes), what happens to the person's neurons or their soul when time is reversed (if you have souls as an explicit part of the setting).
Big time travel spells are even more unwieldy. I think the increasing popularity of multiverses mitigates some of that complexity, but even then, it can really dampen the stakes of a story.
I'm more referring to situations where the effect is not temporary but a permanent change. I was surprised to consider the power that someone gains after being bound to telling the truth, previously only considering something like this to be a curse. The things that person says carry with them so much weight, and the person can be immediately trusted to a large degree.
I couldn't figure out what you were referencing and then remembered the Aes Sedai oath rod.