if you scroll the forums there are loads of people listing mods
Elderscrolls forums for the best info
I found wikipedia - recommended mods - useful as well along with tweakguide for oblivion
mainly replacement texture mods,
gameplay ones its dependent on different people.
err machine guns? are you taking the ****

(cant decide

)
its a fantasy game so your pretty much dealing with medieval style weapons, standard swords, axes, bows etc, you get different types of each and also made from different materials or enchanted ones.
its an open world-ish game where you can do what you want or follow the storyline or do all the other side-quests.
its a good game IMO, less immersive than morrowind but this suits some people better with a bit more tighter scripting.
i would say it isnt anything like final fantasy if comparing the 2 in terms of gameplay, although both are RPGs where you control your stats, skills, inventory, etc.
its worth a go, some people hate it, get bored of it fast, love it, well like most games. general consensus its a good game.