"Live View" refers to a mode in which whatever is seen though the lens appears on the camera's rear LCD in real time. This is very common in P&S digicams, in fact with some digicams it's the only way to aim them since they lack any type of optical viewfinder.
With a Digital SLR, it's not quite to easy to do as you might think. Normally there is a reflective mirror and a closed shutter in front of the digital sensor. The mirror deflects part of the image up into the viewfinder, which is where the metering is done. Part of the image passes through the mirror and reflects off a secondary mirror to an AF sensor unit on the floor of the camera. Since there is no light getting to the sensor (and the shutter is closed anyway) the sensor obviously can't display a view on the LCD.
"Live View" first appeared in a primitive form in the EOS 20Da, a special version of the EOS 20D designed for astrophotography. The fist "main stream" EOS body to use it was the EOS 1D MkIII and it is now available in the new EOS 40D and EOS 1Ds MkIII, though the details of operation are slightly different on each camera.
In Live View mode, the mirror is raised up and the shutter is opened. The sensor can now receive the image produced by the lens and it can be displayed on the rear LCD. Of course the metering system in the viewfinder and the AF system on the floor of the camera are now not seeing the image, so conventional AF and metering are no longer possible in Live View mode. While it is theoretically possible to use the digital sensor to determine focus, the EOS 40D doesn't do that. It can AF in Live View (unlike the 1Ds MkIII, which can't), but to do so it must briefly lower the reflex mirror(s), perform the normal AF function, then raise the mirror again. This is done on the EOS 40D by pressing the "AF on" button on the rear of the camera. Manual focus is also possible of course, and the image can be magnified 5x or 10x for more precise judgment of manual focus.
Metering is done in Live View by the digital sensor using an evaluative metering pattern (which can't be changed), though in the 5x and 10x view modes exposure is locked (and determined by the whole image, not just the displayed portion).
There are two quiet shooting modes in Live View. In the first (mode 1) you can shoot either in either single shot or continuous mode. The mirror doesn't move so the only noise is shutter noise, which is significantly quieter than normal operation in which you get both the shutter and mirror noise for every exposure. In continuous shooting mode the LCD display turns off during the exposures, but returns after the last exposure has been made. In the second mode (mode 2) only single shot mode can be used, but there is essentially no noise when the exposure is made as long as you keep the shutter depressed (there is a very soft "click"). When you release the shutter release the shutter curtains recycle. Using this mode you can take a silent shot and delay the noise until later (as long as you keep the shutter release depressed).
Live View options are set and the operation enabled via the menu system. The ability to use AF during Live View operation is set using Custom Function (C FnIII -6). The "SET" button (the button in the center of the rear control dial) can then be used to turn Live View on and off. An optional grid on the image for alignment purposes can be displayed if selected via the menu system.
One factor I haven't looked at yet is whether extended use of LiveView causes enough heating of the sensor to degrade high ISO performance to any appreciable extent. Live View will certainly drain the battery faster since power is needed for the continuous sensor readout as well as the LCD display.
Live View will probably be more useful for shots with the camera mounted on a tripod than handheld, though I guess there's nothing to stop you using Live View to enable you to shoot "P&S fashion" with the camera held out at arms length. However given the AF limitations, I can't really see a good reason to do that. IS lenses should work just fine since IS is built into the lens and doesn't care what the sensor is doing. I'd imagine that Live View will be useful in situations where the camera is being controlled remotely from a PC using the EOS Viwer Utility, since with Live View you will be able to view the image on the screen before exposure.
It's also going to be useful for macro work where the 10x magnification enables accurate AF and DOF can be previewed and for any situation calling for critical manual focusing. Though not a use many may have, I know it will be useful for me when testing lenses for example.
Live View will also be a great asset for those using telescopes for photography, such as those using a "digiscoping" technique. Telescopes are usually pretty slow (f11 to f16 isn't uncommon) and so focusing on the viewfinder screen can be tricky. With Live View, assuming you have a static subject, manual focus using the 10x magnification should ensure the sharpest possible images. Since the Live View mode locks up the mirror, it will also provide mirror lockup, which is usually desirable when shooting with long, slow lenses.
Finally, for pixel peepers and focus checkers, Live View will enable you to pretty quickly tell if a lens is front or back focusing without having to take a shot!