Yeah, they certainly can add replay value. The main reasons for myself, very rarely getting them is your fore-mentioned fact that that's the way developers created it and adding mods feels like abnormally mutating a complete product (of course that's not always the case, yet this is how I see it as a phenomenon). Other reason being a good mod can be difficult to find. And the time one spent looking for it, installing it and testing it feels very wasted and most importantly at the worst case it can ruin the game a bit for you. A big mod for Battle for Middle-Earth II (which I was playing a lot at the time) almost ruined it for me for some reason.
I really loved Fallout 3 also. That's actually a game I thought of getting mods to, as the constantly breaking weapons and apparel where a slight pain and the weapons clearly felt like they were lacking in power. I even discussed about it with a friend and he had felt the same faults and gotten the mods that really improved the game for him, and probably would've done the same for me too, but I haven't really played it in a while and therefore not acquired the mods.
I suggest you try Oblivion. That's the best way to find out, if you like a game. But if the whole fantasy setting with swords and magic and stuff feels like it won't fit you, then it is probably not for you. Personally I like swords and such melee weapons maybe even more than guns, but I don't think swords versus guns proves to be a tie-breaker of any kind in this case. It's more about the setting of fantasy/realism/future/etc. and note that Oblivion is older than Fallout 3 for example and loses to it in a number of ways (in my books), but then again it is a different game. Oh, and I guess I should mention the clearly greatest flaw in Oblivion, enemies get tougher as your character makes progress. So, let's say the exact same squirrel is stronger and a tougher opponent when you're a level 10 than when you were level 5. I guess there are a bunch of mods fixing this as there are a bunch of other people that this thing bothers as well.