I just saw in youtube there is a ton of "big" mods for oblivion.
Mods are also very important in many other games, Valve is specially famous for this, also maps users created for starcraft are an important part of the online experience, quake had some amazing material, etc.. I'm not much of a PC person, so there may be many better examples, but I guess you get the point.
What I wonder is if this kind of mods will be offered as DLC at some point in consoles, opening the system to some extent. I mean, it would be easy to put it there, and maybe even disable some features as playing online with the mod enabled (the mode itself could be enabled and disabled, or activated and deactivated). The mods would be controlled, by the system company, getting some revenue, etc. Even mods could be tagged and accessible to children (or not) depending on parental settings.
I don't think this will ever happen, but I also think it is quite stupid. Giving the tools to people to do mods that would cost $1 or $2 (there is stuff in XBL for your avatar that is more expensive) would allow them to put their mods in consoles, show their skills for their CV, maybe having some fun (basically everything they get by doing mods for PC and some money), and would give the companies an additional source of revenue, they would get rid of this competitive disadvantage against PC and would be perceived as less evil and controlling, maybe more perverted considering the nature of many of these mods, but we already have Bayonetta, which is not a mod but a full game, so no big deal wrt that.
Consoles provide more control, which is good specially for concerned parents, but some features and capabilities in PCs that could be controlled in consoles are just missing, and that could be improved, IMHO.