I'm also in the camp that some scarcity is good for the (SP) game. It's not really that challenging anyway, so anything that forces me to have an obstacle to overcome and some tough choices to make is welcome. As others have mentioned, there are tons of ways to acquire resources if you really need them - buy them from the AI, become the suzerain of a city-state, use Amani, etc.
In fact, in most of my games where I've played peacefully I find myself drowning in horses and iron (and I don't tend to have that many cities, 6-7 usually) that I don't really need for anything, especially from the mid-game onward (apart from a little bit of iron for railways). I can sell them to the AI, but then at some point they also reach their capacity. I think it would be nice to have some non-warfare use for those resouces, like the power resources later on. Maybe an Arena alternative like a Hippodrome, which consumes 1 horse/turn to give extra amenities, or a Foundry replacement for the Workshop that consumes iron to give extra production.
I do agree that Firaxis probably need to have a look at oil. There are too many units that consume it, including a couple like Infantry and Artillery, that could be unlocked before you even have discovered oil. That's in addition to oil being used as a power resource. (Although, it's probably a good representation of oil being the principal fuel of human civilization in the last century or so.)
Edit: Did a quick check, it turns out that no less than 10 units (not including uniques) use oil. Also, if the wiki is correct, oil does NOT spawn on plains or grassland (except marsh), which are the main types of terrain that you tend to have. There is clearly some rebalancing to be done somewhere.