oracle10g - Oracle 10g: Can CLOB data lengths be less than 4,000?

Question

We have three databases: dev, staging, and production. We do all our coding in the dev environment. We then push all our code and database changes to staging so the client can see how it works in a live environment. After they sign off, we do the final deployment to the production environment.

Now, about these CLOB columns: When using desc and/or querying the all_tab_columns view for the dev database, CLOBs show a data length of 4,000. However, in the staging and production databases, data lengths for dev-equivalent CLOB columns are odd numbers like 86. I've searched for every possible solution as to how this could have come about. I've even tried adding a new CLOB(86) column thinking it would work like it does for VARCHAR2, but Oracle just spits out an error.

Could the DBAs have botched something up? Is this even something to worry about? Nothing has ever seemed to break as a result of this, but I just like the metadata to be the same across all environments.

Answer 1

First of all, I - as a dba - feel sorry to see the lack of cooperation between you and the dbas. We all need to cooperate to be successful. Clob data lengths can be less than 4000 bytes.

create table z ( a number, b clob);
Table created.
insert into z values (1, 'boe');

1 row created.
exec dbms_stats.gather_table_stats (ownname => 'ronr', tabname => 'z');

PL/SQL procedure successfully completed.
select owner, avg_row_len from dba_tables where table_name = 'Z'
SQL> /

OWNER                  AVG_ROW_LEN
------------------------------ -----------
RONR                       109

select length(b) from z;

 LENGTH(B)
----------
     3

Where do you find that a clob length can not be less than 4000?