Oblivion: Shit. Don't get it. Mods can make it playable, but with only 512 ram and 800 mhz you will be lucky to install it within 24 hours. But regardless of that, it's still bad. I don't know why everyone reccomends it, it's basically an MMO for people without an internet connection.
Gothic: It's an old game, so it's not surprising that no one in this thread knows about it. The story is good, but the gameplay and graphics may be too dated for you. What really stopped me from enjoying it was the game controls though, most awful stuff ever. There is a Gothic ll, which is praised, but I've never played it.
Fallout 3: Since people are mentioning Oblivion, I should mention this. For an RPG made in 2008, it's playable. For an RPG, it's pretty bad. There are certain aspects that really ruin the experience, mods can make it more enjoyable, but it's basically unplayable on your computer. I would reccomend Fallout 2 and 1 but those are not action RPGs.
Mass Effect: If you had 2x the processing power and 2.5x the memory, I'd reccomend it to you. Great story, good action/rpg mix gameplay. Comes very recommended by me.
Jade Empire: Very playable, story/characters and combat are less than desired, but perhaps playable on the lowest settings on your rig, but that's a big maybe. I would reccomend it. It's also on XBOX, if you can't play it on the PC (actually, same goes for Mass Effect). Action/Rpg mix.
Icewind Dale/Neverwinter Nights: Not having played these, I cannot reccomend them, however they are generally regarded as "good" if "playable" RPGs, not so sure if they're action-based (pretty sure they're not, more diablo based I think). Note that NWN, Jade Empire, ME, Baldur's Gate Series are all made by Bioware, so if you're into any one of those games, get the rest. Possibly barely playable on your rig, but I doubt it.
KOTOR (Knights of the Old Republic): I tend to classify this as "Bioware's Greatest Gift to Humanity". Okay, it's not godly, but it's one hell of a great action RPG and is worthy of being excluded from the others I mentioned. Great story, fun characters, combat system is enjoyable, good roleplaying elements. Possibly playable on your rig, but I severally doubt it.
KOTOR ll: Made by a different company, however, its development was arrested prematurely so it's greatly inferior to KOTOR. BUT, there is a fan-made patch which restores almost everything in the game that was intended, making the game a fairly enjoyable sequel. The fan-made patch is still unreleased, but you can get a leaked version (1.0b8) via googling for its torrent. It's worth the trouble to get, if you can get the game. It restores content not finished and fixes the bugged ending. You'll probably need the patch for even your first play through. Not playable on your computer.
Vampire Bloodlines: The Masquerade: A commercial action RPG using valve's Hammer engine. A cult following is all it has, but there's a reason for it. Great combat system, great story, great player-building elements. There is a restoration patch for this too, it is worth downloading for a second play through. Unlikely it will work for your computer.
Witcher: A bizarre if shallow plot with bizarre if shallow gameplay featuring bizarre if shallow characters. I would not recommend it, however it's definitely playable. Not on your rig though.
I believe I've covered 90% of the action RPGs worthy of purchase (and those of note that aren't worth it). However, if you wish to really play any of them, it will be necessary to ugprade. So go to a junk yard and find some parts, or rob somebody. That computer should feel lucky it can still handle WC3 and SC.
This is coming from someone who used, as his primary computer for eight years, 800 MHZ and 1 Gig of ram. It's only within the first year and a half that I've had a computer powerful enough to really enjoy PC gaming.
Ah yes, just like my first computer.
6GB hard drive ftw!
6 Gigs? Wow you're spoiled :9