A medium creature can use a Large Longsword as if it were a two-handed weapon with -2 penalty. A Large Greatsword (or other 2-h weapon) would "step up" into "unwieldable" by default. Likewise, a Large Dagger would count as a 1-h weapon (so you can two-hand it for 1.5x str to damage). A Huge dagger would be wielded as a 2-h weapon at -4 penalty for 2 steps of difference, but you could not wield a Huge Longsword. Gargantuan weapons of any category are right out.
There's a barbarian archetype called the Titan Mauler which reduces those attack penalties, but does nothing for the handiness step-up so you can reduce those -2 and -4 penalties down to -0 eventually, but the Large Longsword is still wielded as a 2-h weapon.
There are other very specific cases which override handiness. The Redcap is a small creature that wields a medium scythe because it has an ability that reduces all penalties for wielding medium weapons in particular. So the Small Redcap wields a Medium Scythe as a 2-h weapon with no attack penalty, but a Large Dagger would still count as a 2-h weapon with -4 penalty. One variant feature for the Tiefling, Large Limb, does the same except for Large weapons for the Medium Tiefling. Sometimes, it's phrased as "1 size category larger" in the case of templates so that it adjusts to the base creature size you apply it to. However, these are eclectic options that rarely come up so, for all practical purposes, you can easily disregard them.
