Depending on the game, a super weapon can be a couple of different things.
A superweapon can be used to drive a story for example. Since superweapons are known for being very very powerful, the villain in a story usually tries to gain control of it and use it for evil deeds. A superweapon can be a robot, a bomb, a space station, a satellite, a gun, etc. Sometimes superweapons must be built by using other weapons; sometimes the protagonist may be looking to use the superweapon against their enemies.
In other games, a superweapon may be awarded to the player for skill. This can happen in both single player and multiplayer games. A game may give players a superweapon with limited ammo to defeat a specific enemy, usually a boss. In a multiplayer game, the player may be rewarded with a weapon that will turn the tide of the battle in their favor (example: the tactical nuke in Modern Warfare 2).
From a gameplay perspective, super weapons aren't usually playable. When they are though they usually feel heavy but powerful. These gameplay sections are usually easy romps through a level with very little chances of dying or getting hit. Sometimes a game will pit two super weapons against each other which usually means a drastic change in the gameplay and controls.