LOB's -- How Many Out of Line?

  • From: "Larry Elkins" <elkinsl@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Jan 2005 12:25:09 -0600

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@xxxxxxxxx
214.954.1781


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

Other related posts: