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

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 开发者_如何学Cdoes 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.

---

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?

---

DATA_LENGTH stores the maximun # of bytes that will be taken up within the row for a column. If the CLOB can be stored in row, then the maximum is 4000. LOBS will never take up more than 4000 bytes. If in row storage is disabled, then the LOB will only store the pointer information it needs to find the LOB data, which is much less than 4000 bytes.

SQL> create table t (clob_in_table clob
  2     , clob_out_of_table clob
  3  ) lob (clob_out_of_table) store as (disable storage in row)
  4     , lob (clob_in_table) store as (enable storage in row)
  5  /

Table created.

SQL> select table_name, column_name, data_length
  2  from user_tab_columns
  3  where table_name = 'T'
  4  /

TABLE_NAME                     COLUMN_NAME                    DATA_LENGTH
------------------------------ ------------------------------ -----------
T                              CLOB_IN_TABLE                         4000
T                              CLOB_OUT_OF_TABLE                       86

EDIT, adding info on *_LOBS view

Use the [DBA|ALL|USER]_LOBS view to look at the defined in row out of row storage settings:

SQL> select table_name
  2     , cast(substr(column_name, 1, 30) as varchar2(30))
  3     , in_row
  4  from user_lobs
  5  where table_name = 'T'
  6  /

TABLE_NAME                     CAST(SUBSTR(COLUMN_NAME,1,30)A IN_
------------------------------ ------------------------------ ---
T                              CLOB_IN_TABLE                  YES
T                              CLOB_OUT_OF_TABLE              NO

EDIT 2, some references

See LOB Storage in Oracle Database Application Developer's Guide - Large Objects for more information on defining LOB storage, especially the third note that talks about what can be changed:

Note:

Only some storage parameters can be modified. For example, you can use the ALTER TABLE ... MODIFY LOB statement to change RETENTION, PCTVERSION, CACHE or NO CACHE LOGGING or NO LOGGING, and the STORAGE clause.

You can also change the TABLESPACE using the ALTER TABLE ... MOVE statement.

However, once the table has been created, you cannot change the CHUNK size, or the ENABLE or DISABLE STORAGE IN ROW settings.

Also, LOBs in Index Organized Tables says:

By default, all LOBs in an index organized table created without an overflow segment will be stored out of line. In other words, if an index organized table is created without an overflow segment, then the LOBs in this table have their default storage attributes as DISABLE STORAGE IN ROW. If you forcibly try to specify an ENABLE STORAGE IN ROW clause for such LOBs, then SQL will raise an error.

This explains why jonearles did not see 4,000 in the data_length column when he created the LOB in an index organized table.

---

CLOBs don't have a specified length. When you query ALL_TAB_COLUMNS, e.g.:

select table_name, column_name, data_length
from all_tab_columns
where data_type = 'CLOB';

You'll notice that data_length is always 4000, but this should be ignored.

The minimum size of a CLOB is zero (0), and the maximum is anything from 8 TB to 128 TB depending on the database block size.

---

As ik_zelf and Jeffrey Kemp pointed out, CLOBs can store less than 4000 bytes.

But why are CLOB data_lengths not always 4000? The number doesn't actually limit the CLOB, but you're probably right to worry about the metadata being different on your servers. You might want to run DBMS_METADATA.GET_DDL on the objects on all servers and compare the results.

I was able to create a low data_length by adding a CLOB to an index organized table.

create table test
(
    column1 number,
    column2 clob,
    constraint test_pk primary key (column1)
)
organization index;

select data_length from user_tab_cols
where table_name = 'TEST' and column_name = 'COLUMN2';

On 10.2.0.1.0, the result is 116.
On 11.2.0.1.0, the result is 476.

Those numbers don't make any sense to me and I'd guess it's a bug. But I don't have a good understanding of the different storage options, maybe I'm just missing something.

Does anybody know what's really going on here?