On 'Finding Nemo' type visuals- the Viz work for those types of projects tends to be done on either SGI Irix machines, HP-RISC based workstations or if they are using x86 based PCs nVidia based Quadros(nVidia dominates the workstation market for x86 at this point). The final rendering that you see outputted is done using Pixar's proprietary build of RenderMan in software- currently handled by a render farm of Xeons(Finding Nemo was not done on that setup, they didn't have it yet) packing over 1K processors IIRC.
What kind of vid card would it take to handle Nemo- the big problem with it would be the radiosity effects in the movie. It will be years before we see a card that can handle even basic levels of radiosity in hardware, and it tends to be more computationaly intensive then rendering the entire rest of the scene.
For everything outside of the radiosity current nV hardware could get pretty close although not in real time, not remotely close to anything resembling real time(FP32 is needed with that level of shaders). ATi could do an approximation but you would end up seeing some artifacts from their limited precission. NV40 should be much better in that respect(PS 3.0 support), although the speed of it will still be far too slow. DXNext level hardware from both ATi and nVidia should actually be able to show us something that looks very close in real time- although it won't be at anywhere near the film resolution of the movie it could compare decently to the DVD(minus radiosity).
GPUs are already starting to show advantages over CPUs for off line rendering- it won't be too much longer before they are replacing CPUs for a good deal of the rendering process.
That kind of graphics card doesnt exsist, it would take over 4 gigs of memory on the vid card itself to store that much texture and FP data.
If you want real time with that level of detail you use procedural textures, shaders, and higher order surfaces. Offline CGI does things stupidly talking in relative terms, but they want absolutely no compromises and have the money to spend to assure it.
You aren't going to do that in realtime anytime soon.
One of the big demands there is the level of resolution they are dealing with, film. For DVD level quality you could reduce their loads by a couple orders of magnitude.