LOB's -- How Many Out of Line?

Listers,

Assuming a CLOB, for example, was defined with ENABLE STORAGE IN ROW. Is there an easy and *fast* way to determine how many of them are out of line? Applies to both 8.1.7 and 9.2.0.5.

Ok, even with in-line storage, the CLOB would be stored out of line if it exceeds 4K (actually 3964 according to various Metalink documents). So I know I could do something along the lines of "select count(*) from table_with_clob where length(clob_column) > 3964". Ok, so that *is* easy, but what I was really looking for is some sort of dictionary object that might tell me this, much like how gathering stats will give us the number of rows (at the time the stats were done), chain_cnt (analyze only), etc. I can always get the number of used blocks for the LOB SEGMENT through DBMS_SPACE or dictionary views, but that doesn't necessarily tie back to the number of rows.

I've been going through all the dictionary views, as well as SQL.BSG for detailed information on the base sys.XYZ$ objects, and I'm not seeing anything that could help me here. I was hoping that similar to how analyze can record the chain count, that there might be something gathered by the stats process that would indicate the number of rows where a LOB had to go out of line, and that it simply wasn't exposed through any of the dictionary views. But going through SQL.BSG I don't see anything indicating where such information might be stored.

Any ideas? Have I missed something obvious ;-)

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

--
http://www.freelists.org/webpage/oracle-l