Very vaguely speaking, the circle of confusion is the size of a blurry spot produced by a less than perfectly focused lens that you will still think is in focus. Looking at the overall picture, the human eye and brain are "confused" into thinking that the spot represents something still in focus.
To some extent it is subjective.
The charts mentioned above refer to the size of the spot (0.005mm or so) on the camera's sensor which could be as small as a quarter of an inch across. When the picture is enlarged to typical dimensions such as 4x6 inches or 8x10 inches or (on a movie screen) 4 feet across, the absolute size of a circle of confusion is much greater. Also roughly, the circle of confusion for most people is around 1/1000'th to 1/2000'th the diagonal of the picture seen at normal viewing distances.
For some cameras the pixels are so coarse (too few megapixels or film too grainy) or the lens is of such poor quality that nothing can be put in focus sharper than the circle of confusions as predicted in the aforementioned charts suggest.
For any camera, only subjects at a specific distance are realy in focus. If we take circle of confusion into account, those subjects whose tiny details are blurred no greater than the diameter of the circle of confusion are within a distance range (reflecting depth of field) as opposed to at one specific distance.
In adjusting a camera for the desired artistic result, the photographer wants certain material "in focus" and certain material (often a background) "out of focus". Often this can be done by experimenting and eyeballing (and with a camera that allows a lot of manual adjustments) without having to calculate circles of confusion.
Digital camera hints:
http://members.aol.com/ajaynejr/digicam.htm