Right now I've got a 9800 Pro, and its poor performance with Oblivion is making me realize that I want to upgrade.
Like so many other people out there. You're definitely not alone.
I've recently learned of the X1600, but my roommate continues to recommend the X800 or X850... the 1600's 512 megs sound VERY tempting, but I need a second opinion (or third, or fourth, or fifth, etc).
The 512MB makes little difference now, and may only be useful in Vista/DX10 and maybe in aid of Physics calculations, but even that's a big 'maybe'. For now if the difference is $20 or less from 256MB to 512MB, then get it otherwise don't waste your money.
If the AGP's on its way out, what's the card I should go for to get the best of the last?
IMO best bang for your buck is the X1600Pro. It's a fair amount slower than the GF7800GS, but also half the price. It's nexk and neck with the slow GF6800GS in it's AGP (unless you're lucky enough to get one that unlocks). In other games the GF6800GS is faster though, but the newer games will likely play closer to morrowind closing the gap a bit and not allowing partial precision fallbacks to help. The X800XT/X800XL/X850XT will almost always outperform the GF6800GS, especially in Oblivion, however you do miss out on the OpenEXR implementation of HDR in Oblivion (X8 series can do HDR in other apps that don't call for FP16 blending). The GF7800GS and especially GT-ified Gainward version are the most powerful, but they are also very expensive. IMO I'd say either ditch your MoBo and get a solid PCIe card like the GF7900GT/X1800/X1900 or even GF7600GT for nearly the same price, or else save some money go with the X1600Pro which will let you pay into the DX10/Vista cards, and then upgrade your MoBo when there's native AM2+DDR2 support, plus many other board/HD and peripheral features.
It all depends on what your main focus is. If Oblivion is your focus over the next few months then the X1600Pro will be the best bang/buck, if you plan on playing a wider variety of new and older games, then something more uniform in it's playability may be in order.
Of your options the X1600Pro has the best features overall, but it just doesn't have the most grunt, however will play Oblivion with the slider up and with HDR @ 1024x768 without hiccups (even at the gates), which is the same level as the X800GTO-X800XL and GF6800GS-GT.
BTW, you don't need new RAM or new RAM to get a new board, there are not only PCIe version for S478, S754, S775, S939, but even AGP+PCIe models, and something to look at with some future upgradability is the MoBo Cleeve, Pauldh and a few others (including my best frined in Toronto) own, the AsRock S939 Dual SATAII which has both AGP+PCIe, plus support for a future AM2 riser, and many great features, and would support your current hardware without change. It's not a wicked overclocker, but it's a good solid mid-step for you IMO.
I'd say get that and then a better PCIe option (true GF7800GT, GF7900GT, X1800GTO-XL, X1900) if you are going to spend alot of money on a crippled or more overpricced GF7800GS, or else go with the X1600Pro.