Sure, for data warehouse fact tables it's very effective. For OLTP tables it's less so, partly because you need to use direct path insert to achieve compression, and partly because OLTP systems generally query individual rows.

There's a few "gotcha's" -- can't add or drop columns from them, for one thing.