[3.PF] Favorite non-core spells in thematics?

[Nanashi]

The really good spells get a lot of talk, even if they make no sense thematically. How about the flip side: Spells you think are neat, even if they don't necessarily work well (spells that are cool thematically as well as mechanically are fine). If a spell is multi-purpose and "cool", even better.

3.5
Extract Water Elemental is a really fun idea, but it's just single target damage with a very conditional bonus. If it made the target exhausted (fatigued on successful save)  and the damage couldn't be cured until the victim hydrated, like the actual thirst rules, it still wouldn't be a great spell for the level, but it would be something.

Fire Wings had a good idea of balancing a lower level flight spell by preventing use of the user's arms with flying, except it's a level 3 spell with the same duration as fly with the same duration and exclusive to the class that has really easy access to hours long flight.

Other WotC d20:
Zap from d20 Modern Dark Matter had the misfortune of being stuck in a super obscure book for a system few people played. In real D&D this would have been a thematically nice damage spell with a decent rider (electric damage to one target with chance to make them prone), but in a system where direct damage is even worse and everyone with hands is a ranged attacker its awful.

Pathfinder:
Early Judgment is pretty cool. Its a rare instance where something actually interacts with alignment and a combat divination spell that doesn't feel forced on either. The problem is that close range, single humanoid target,  1d4 round duration is just complete crap when Hold Person is on the same spell list and is better in every way (better range, better duration, better effect, same restriction on target, same save, same SR, no religion restriction) for evil and good aligned targets, and almost certainly better for neutral targets too. Even with it being level 1 for Witch it's not really worth it.

Accept Affliction would be a great spell... if it wasn't a level 3 spell, the point where you get spells that can cure everything it removes outright. 

[zook1shoe]

a few that come to mind

Flesh to Ooze, Irradiate, and Walk the Plank

[Nanashi]

They really over-valued the sharks part of Walk the Plank. Yes, the sharks are each Summon Nature's Ally 4 equivalent, but they have lost 90%+ of the appeal summoned monsters are supposed to have and the spell has half the point of create pit.

[zook1shoe]

thematically, its fun.... but not very good otherwise

[ketaro]

Instant Diversion from Races of Dragon is a pretty cool 1st level Swift Action spell. Its like a 1 round mirror image that just scatters in every direction. Its a fun mental image.

[Power]

The real power of Walk The Plank is just trying to drag unsuited enemies into an aquatic battle, especially since it has a much wider AoE. That's still not too impressive for a 5th level spell though, but you can also one-shot fire elementals with it, so there's that. The only problem there is that reflex is the fire elemental's strong save, so you may need another way to push it in.

Greater Shadow Transmutation probably wins a prize for looking much more useful than it actually is, essentially being Greater Polymorph with a 2 level tax (9th level spell now, huzzah) and a whole slew of disadvantages.