I've played Civ3 long enough that I've played it on several different systems. You don't really need great specs though, aside from processor speed and cache for huge maps. And aside from really old systems and those with small SSDs, mod size usually won't be a problem (although there are a few > 1 GB mods).
So in order from played-most-Civ3-on to played-least-Civ3-on (leaving out specs that are way more powerful than need be):
1. Pentium 4 2.66 GHz, 512 MB RAM (later 1024 MB), GeForce 4 MX 440, 120 GB HDD, Windows XP. Pretty good, but a bit underpowered for super-sized maps. Main PC till mid-2007; I played most of my Conquest of the World story on this despite it being a very big map. Now retired.
2. Core 2 Duo at 2.2 GHz, 160 GB HDD (later 320 GB), Windows XP. My trusty laptop. Better with gigantic maps. Still used fairly regularly.
3. Core i5 2500K at 3.3 to 3.9 GHz, 2 TB HDD, Windows XP. My current desktop. Better yet with gigantic maps, although it's still possible to get long IBT times on sufficiently big maps with sufficiently many civs.
4. Pentium II 450 MHz, 128 MB RAM, 8 MB Matrox Millenia GPU, 13 GB HDD, Windows 98. What I had when I got Civ3. Didn't work at first, probably due to old drivers, but got it working (albeit with somewhat choppy graphics) about 5 years later. Now retired.
It's also worth noting that having multiple cores doesn't help with Civ3. A second one might help slightly since background tasks can use it, but Civ itself can only use one core of a CPU. So get the best per-core CPU that fits in your budget if Civ3 is your primary game, and forget about pretty much everything else.