Quick Question: Finding fragmentation of a table having clob.

A user has posted me "how to find the fragmentation for a table that contains CLOB", ironically all the queries and other stuff will lead to confusion or no more useful.

Honestly, to answer this question I have searched many things to clear up, only Jonathan has a clear cut demonstration and answer for it. Here is the piece of code,Jonathan has used to demonstrate the purpose.

create table t1(
    v1    varchar2(10),
--    l    long,
    l    clob,
    v2    varchar2(10)
) tablespace users;



insert into t1 select lpad(rownum,10,'0'), rpad('x',100,'x'), lpad(rownum,10,'0') from all_objects where rownum <= 1000 ;

After collecting the statistics,

	SQL> execute dbms_stats.gather_table_stats('TEST','T1');

	PL/SQL procedure successfully completed.

	SQL> select
	       blocks, num_rows, avg_row_len, pct_free,
	       ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) blocks_needed
	    from
	       user_tables
	    where
	       table_name = 'T1'
	    ;
	  2    3    4    5    6    7    8
	    BLOCKS   NUM_ROWS AVG_ROW_LEN   PCT_FREE BLOCKS_NEEDED
	---------- ---------- ----------- ---------- -------------
		27       1000         225         10            32

Table showing it requires 32 blocks instead of 27 blocks, as per avg row len calculation.

Note:- The dbms_stats includes other stuff like lob locators etc and makes the avg row len higher than it has.

To estimate the correct size or avg row len, you will need to use deprecated "analyze command" to gather statistics on lob columns

	SQL> analyze table t1 compute statistics;

	Table analyzed.

	SQL> select
	       blocks, num_rows, avg_row_len, pct_free,
	       ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) blocks_needed
	    from
	       user_tables
	    where
	       table_name = 'T1'
	    ;
	  2    3    4    5    6    7    8
	    BLOCKS   NUM_ROWS AVG_ROW_LEN   PCT_FREE BLOCKS_NEEDED
	---------- ---------- ----------- ---------- -------------
		27       1000         162         10            23

	SQL>

Solution: Use table move or alter table shrink in latest versions