What is a "Mod"?
A mod is short for "Modification", which does exactly that. Oblivion mods can add new weapons, armor, textures, clothing, monsters, change the AI, change how the weather works, add new quests and dungeons, add new playable races, modify the economy, change your default body type; you name it. There are literally thousands of wonderful mods out there to make your gaming experience unique.
How can I get mod(s) into my game?
Oblivion mods are downloaded and then extracted to your Oblivion/Data directory. They usually come in .esp (elder scrolls plugin) and can be managed either through the pop-up Oblivion shortcut on your desktop under "Data Files", or better yet through an open source program called "Oblivion Mod Manager" by the talented Timeslip. You can get the latest version of OBMM here. Also note, not all Oblivion mods are .esp's; some mods, like textures, do not have the necessary .esp file. Instead, you unzip a series of files into the necessary folder.
PLEASE FULLY READ ALL READMES BEFORE ATTEMPTING TO INSTALL A MOD. They provide tons of information on how to install, run, play, and uninstall your particular mod. They might also answer some general questions if something goes wrong with your game.
How difficult are they?
Oblivion mods are very easy to install. Just drop it in your Oblivion/Data directory and you're good to go. Please note, if you're installing a texture mod, you WILL NEED TO USE OBMM. After unzipping the files to the directory, start OBMM and click on Utilities -> Archive Invalidation and then "Update Now". If you don't, your new textures may not appear in-game.
Where can I get them?
There are numerous websites and communities around the internet where you can download mods for Oblivion, including this very site. Some of the larger modding communities include:
How can I learn to mod?
There is one definitive place to learn how to mod for Oblivion: The Elder Scrolls Construction Set Wiki. It's a wiki by the community, for the community, comprised of all sorts of tidbits and information needed to be able to work the Elder Scrolls Construction Set, which is the main tool used for modding Oblivion. You can also pester the fine folks over at the official Elder Scrolls Forum.