Many people think of RPGs as a form of "power fantasy", in which the goal is to be awesome and powerful. This is an easy assumption to make, since many RPGs allow characters to wield magic, psychic powers or the Force or play with awesome futuristic technology. Sometimes all at once.
But then you get little oddities, typically in horror games. Both Call of Chthulhu and World of Darkness (without splats) feature characters that are remarkably mundane. In CoC, characters can learn magic, but only at the cost of their sanity. Even specific campaigns can create this effect. Over on the Steve Jackson Games forums, William H. Stoddard and one of his players will recount a game they played in the Transhuman Space setting in which the characters were marvelously mundane people and the game was all about the drama of ordinary people living in a very big, scary, technologically advanced world. No heroics, no earth-shattering revelations, just people leading very complicated lives.
Where's the fun in that sort of thing? Where's the epic awesome? You're not going to find it. Because when you get down to it, roleplaying is not "power fantasy", but "empowerment fantasy." Action oriented games allow us to play the hero, letting us be the one who saves the world, slays the dragon, and wins the heart of a princess. In more mundane genres, we might simply acheive a cathartic "safe space" in which we may explore things that we can't do in real life. Much like a horror movie, a horror RPG lets us experience the horror from a safe distance, allowing us to walk away at the end of the show and laugh about it. A dramatic game can also benefit from this, as an unmarried player might try out married life and its trials and tribulations. The player can make all the mistakes he wants in the scenario with the knowledge that he can get up from the gaming table and be his freewheeling, single self again.
Exalted is a game that actually plays both sides of this fence. Characters wield charms and spells that make them quite powerful, playing neatly into power fantasy. But because of this level of power, characters are very much their own masters. Which can make the experience rather empowering, as well.