After effects, shake, combustion, are probably the most common compositing applications.

think theres also a free compositing Application, but I cant remember the name of it.

You can also do some in Adobe premier, Avid express pro, Final cut pro, but they dont have the level of "tweekability" and blending options that the compositing apps do.