I'm a big fan of getting exposure right in camera--or at least as right as possible. The old maxim of garbage in/garbage out applies as well to digital imaging as it does to any other subject. The better your exposure, the more you can do with it later. But sometimes you get a crap exposure, one that really stinks.
For example, I caught the following image a few weeks ago at a party with my Canon G9 for the upcoming Web 2.0 Expo New York:
As you can see, it's underexposed. Really underexposed. It's pretty much a tosser by any standard. I'm pretty sure I was playing with an external flash on the camera to and working with light levels, so I didn't really intended for there to be any kind of shot here. But, on a lark I decided to see how well Aperture 2's Highlights & Shadows tool worked on an exposure like this. After all, when you know what the tools can really do in extreme circumstances, you can better apply them in more normal situations. Not really wanting to be subtle about it, I cranked up the Shadows adjustment all the way.
The result is fairly surprising:
You can identify people and objects in the picture that were lost in the darkness before. It's still a crap shot and not a keeper. But just imagine if I needed to see what was in this picture for some other reason than artistic. It just goes to show that there's a heck of a lot of data tucked away in the shadows of those RAW files and, even if it's not pretty data, it can be useful.
Update: Greg Newman pointed out in the comments that it was hard to see what kind of noise was in the resulting image. Here's a 100% crop of the woman in the image:
The light fared a bit better:
Keep in mind that these are off a G9 which has a decent sensor in it, but it's nothing like the sensors in modern DSLRs. It'd be interesting to see what one could get out of a 5D,a 1Dmk3, or a D3.

It’s hard to see from the size of these images. Did you get alot of noise by cranking the shadows to 100?
You may want to fiddle with new black point tool on this image, too.
The advanced settings in the Highlights and Shadows really help correct for contrast and color after you have pulled detail out of the shadows. Its worth taking a look at them.