Great Swords
As with swords, the term "great sword" is really a generic term applied to a whole class of edged weapons. When a sword was long enough that it could no longer be wielded one-handed it was then considered a "long sword' or "great sword" and meant to be used in a two-handed fashion (one near the top of the hilt, one closer to the end). These swords came in a variety of styles from the Scottish Claymore to the Germanic Zweihander (literally meaning "two-hander") but all with a distinct purpose: swinging.
The thing to remember with swords is that their intended purpose, at least for the shorter blades, was to swing and stab. The swings of your standard sword would be short and fast, meant to get in, deal damage (bashing against armor or helmets), before a thrust with the tip of the sword could be used (or the enemy was knocked down so a dagger could be thrust into a gap in the armor). A great sword is different, though. Due to its length it was not meant to be a fast blade, nor was it great for stabbing. Instead, a great sword has a much longer arc to its swing. Although not economical in its movement, the general force of a great sword (bashing against armor or taking off unprotected limbs) granted its own usefulness in battle (for those that chose to swing one).
Realistically it took the right warrior to swing a great sword, and even then you were leaving yourself open to attack as you swung your giant blade.
About Great Swords:
History of Swords
Great swords game into use in the Castlevania series in, of course, Castlevania: Symphony of the Night. That game introduced (or brought back) a number of weapons that then became mainstays of the series, and the great sword was no exception. Although nowhere near as common as the more standard swords, great swords could be swung by hero Alucard two-handed, and generally their damage output was substantially higher than that of single-handed swords (at the expense of Alucard being able to equip a sword). It is worth noting that some two-handed swords in the game had the traditional over-head swung of a great sword, but others were sung like standard swords (even though they used both hands). These swords were faster in their attacks but did still require the loss of a shield.
Great swords made their return a few games later in the next Castlevania game to not have a Belmont (or Belmont descendant) in the ranks: Castlevania: Aria of Sorrow. Like with Symphony of the Night, the great swords in Aria were slower but strong weapons, swung overhead in a long arc. The difference here, and with great swords in all future games, was that the various hands of the hero were no longer a consideration; hero Soma Cruz could only have one weapon (and no shields at all) equipped, thus it didn't matter if a sword was considered "one-handed" or "two-handed" as they all effectively worked the same in the weapon equip screen. This laid the standard for great swords for all future games in the series: Castlevania: Dawn of Sorrow, Castlevania: Portrait of Ruin and Castlevania: Harmony of Despair.
Great Swords in Combat
In Castlevania the great swords are slower weapons than standard swords. They have a longer slash, generally conveyed as an overhead attack with a long (but damaging) arc. These swords, due to their slower attack speed, can leave the heroes vulnerable to attack during their swings. However, due to their larger size they generally deal more damage as a consequence.
it is worth noting that many great swords in the series can be jump-canceled (i.e., the sword can be swung in mid-air and when the hero lands the attack animation will cease). This is a good way to layer on damage without leaving the hero quite so vulnerable to counter-attack. Additional some great swords can also be dash-canceled, allowing a dash to be performed during the attack to stop the animation.
Historic Great Swords
Claymore
A traditional Scottish two-handed sword, often considered the genesis for European great swords (or at least the inspiration for other similar weapons).
Estoc
A French sword wielded two-handed, meant to be used against armor. The sword itself was blunt on the sides, giving it a bashing purpose against armor, with just a sharpened point for thrusting.
Flamberge
The flamberge (or floberge) is a two-handed weapon with a distinctive "wavy" edges to its blade. The term "flamberge" would eventually become common use for a variety of long swords.
Obsidian Sword
As the name would suggest this was a sword made of obsidian. Within the confines of the Castlevania series this was likely a magical sword as obsidian would likely shatter against armor. That said, in many older cultures obsidian weapons were made, generally but sticking sharpened obsidian in between hunks of wood, creating spears and daggers from the material. While the weapons would break against metal they were quite effective on flesh.
Zweihander
A German two-handed sword. Interestingly the zweihander was both the pinnacle of the two-handed sword and also its final form as, past this point, these swords became so long, and so unusable, that they ceased being practical at all in battle, having the length of a polearm with less maneuverability.
Great Swords of Legend
Ascalon
The holy sword (or in some versions of the tale, spear) that Saint George used when he slew the Dragon of Silene. Of course, it was Saint George's convictions, more than the weapon itself, that was truly holy in the tale.
Balmung
Sometimes called "Gram" (although in the game series these are two different weapons), the Balmung was the sword of Siegfried in the German epic "Nibelungenlied". Of note, Siegfried dies in that tale but we won't hold that against his sword.
Claimh Solais
An Irish sword that belonged to Tuatha De Danann, mythical king of Ireland. In the stories the sword glowed with holy light. Interestingly, while the sword is not pronounced as "claim souls" is certainly looks like that's the name of the blade, a fun choice for the ultimate weapon in Aria of Sorrow, a game about collecting souls.
Durandal
Supposedly given to Emperor Charlemagne (at least, in epic French poems), the sword was then passed to the legendary paladin Roland. it was said to not only be a powerful weapon in battle but also indestructible.
Excalibur
The legendary sword in the stone, which could only be pulled from said stone by the One True King (aka, King Arthur). Amusingly, in Aria of Sorrow the sword is still in a big chunk of boulder and hero Soma swings the whole thing. This gives it a big reach, making it a slow but very effective weapon.
Sword of Dawn
Also known in the series as "Daybreak", this sword can summon ghostly allies to fight (with the right button combination: down, down-forward, forward plus attack). Fan conjecture is that the sword comes from the books of Michael Moorcock as his Hawkmoon novels have a weapon, the Sword of the Dawn, that an summon Aztec ghosts into the fray.
Other Great Weapons
Along with great swords the series has also introduced another class of weapons swung a similar way: battle axes. Unlike the traditional sub-weapon axe, which is thrown, the battle axe is swung overhead, using the same animation as a traditional great sword. This renders the two types of weapons effectively the same within the confines of the Castlevania series. Additional weapons that are swung like an axe (or great sword) include:
Battle Axe
Coming in both one- and two-handed versions, from the Danish axe to the sparth axe, battle axes were specifically meant for combat, not utility, and could be swung not unlike a sword (although lacking the thrusting point).
Death's Sickle
A powerful axe-like weapon in the series, this is the weapon naturally favored by Death. Given the Dark element in the series, this sickle (or scythe, in later versions of weapon in the series) is used to cleave the souls from bodies at their time of dying.
Terror Bear
A goofy weapon, the terror bear is a stuffed bear filled with iron sand. It doesn't do amazing damage but it can multi-hit enemies when struck, allowing for solid damage stacking. There is no known mythological (or pop-culture) reference for the bear.