Several of Ben's alien forms qualify. Some, like Ripjaws or Way Big, can only be used in very specific environments while others, like Heatblast, have a huge risk of collateral damage. But crown for this goes to Alien X. A Celestialsapien, Alien X is capable of reshaping the very fabric of the universe at whim. But there's a catch: Alien X has three separate personalities, Serena, the voice of love and compassion; Bellicus, the voice of rage and aggression; and Ben, the voice of reason. In order to perform any action at all, up to and including speech and physical movement, two of those three personalities must agree to do so. Considering the other two personalities have been arguing for an eternity before Ben's arrival and aren't likely to be convinced by a fifteen year old, this doesn't happen very often. For example, when he arrives they present him with the question of whether or not they should save the dinosaurs. To them this is not an impractical question. They could reverse time and save the dinosaurs. Although Ben, having evolved from creatures who thrived in the wake of the dinosaurs' extinction, would create a paradox (No, not the time traveler) if he did so.
The Omnitrix was this throughout most of the original series. The most powerful weapon ever created? check. Capable of adapting to any situation? check. Nearly indestructible? check. Questionably reliable battery life, target for the most dangerous criminals in the universe? ummm... check. The Ultimatrix and completed Omnitrix only amped up this trope.
Invader Zim runs on this trope. One of the biggest examples is in the episode "Battle of the Planets." The Martians worked themselves into extinction turning Mars into a giant spaceship. When Zim asks the Martian hologram why they did that, it said "Because it's cool!"
Many things in Adventure Time can be considered this. Two examples are a Gauntlet Dock and a power gauntlet that is too big to carry.
The Technodrome from the original Teenage Mutant Ninja Turtles cartoon wasn't just awesome - it was badass. It looked both cool and formidable, was capable of surviving in any environment from outer space to under the ocean, and was equipped at various points with a time machine and a fully fledged robot army. One problem, though—there was no power source capable of powering the Technodrome for any practical length of time, making the vehicle essentially worthless. The two biggest storylines over the course of the series were Shredder's attempts at killing the Turtles and trying to jump start the Technodrome.
In Turtles Forever, once the 200X Shredder applies some of his own technology, he's able to unleash the machine's true potential and nearly destroys the entire multiverse with it. Even more awesome is that it becomes the only thing powerful enough to stop the super-sized Shredder!
In The Venture Brothers, a series set in a universe that runs on Awesome but Impractical, one particular item stands out: The Monarch's Battle Panoply. It is a solid suit of complete body armor that happens to restrict 100% of his body movements and makes him spin around uncontrollably in flight, shooting lasers everywhere. To deploy it in the field, two people had to help him don the armor, and then his henchmen had to move him around like a statue. One henchman even died deploying the wings. Naturally, this didn't end too well for him. Justified somewhat in that it had never been tested.
In a complete inversion (i.e., Lame, Yet Practical), as well as a case of Chekhov's Guns, we have the wings worn by the Monarch's henchmen. For nearly two seasons, they show no purpose other than being part of the costume and the butterfly motif. During the final episode of season 2, however, it turns out the wings can fly. You heard right: the wings actually double as jet packs, allowing the henchman to fight off Phantom Limb's army. However, after this, it is never brought up again. A goofier aspect of this is brought up in season 4; the wings are constantly extended, and every door in the Monarch's cocoon base tapers to a point at the top. The henchmen can barely walk around without their wings getting in the way.
A lot of Megatrons from different series turns into different modes. Beast Wars had a T-Rex mode. The Unicron Trilogy Megatron had turned into a tank, a jet and a race car etc. G1 Megatron, by far the most memorable Megatron however turns into... a pistol. Never mind the fact that he could shoot at his enemies when he's in robot mode anyway or the fact that every one of his minions had guns of their own, but when he turns into the pistol he will need someone to actually wield and use him (so that means 1 less fire support) and also his gunbarrel turns significantly smaller so the damage output he makes would likely be less then if he just aimed his Arm Cannon! Seemingly the only advantage of his gun mode would have been if he wanted to inflict a more precise wound on someone.
Particularly large Cybertronians like combiners, Omega Guardians or the Titans. Living weapons bristling with an army's worth of firepower but also have such a high energon bill that they're not usable in the long term.
Megatron's fighting style has shades of this. Unlike every combatant, he's only armed on his right side. As Optimus demonstrates, in the Season 2 finale, if Megatron's right arm were to be cut off, he'd be without a weapon. While Megatron's raw strength is usually enough to make up the difference, Optimus' Boring but Practical fighting style is generally shown to be the superior approach.
The Artifacts of the Primes in the context of the larger war. As powerful as they are, they're unique and can't be given out to every member. While the Star Saber nearly brought down the Nemesis, when Megatron forged his Dark Star Saber, Optimus was forced to abandon decoding the other Iacon entries to confront Megatron with the Star Saber.
The Apex Armor might be the best example. While it's Made of Indestructium, it's also very slow and the user can't access of their internal weapons.
Quotes • 
Headscratchers • 
Playing With • Useful Notes • 
Analysis • 
Image Links • 
Haiku • Laconic
