Oracle 10g：CLOB 数据长度可以小于 4,000 吗？

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

提问by oscilatingcretin

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.

我们有三个数据库：dev、staging 和 production。我们在开发环境中完成所有编码。然后我们将所有代码和数据库更改推送到暂存，以便客户端可以看到它在实时环境中的工作方式。在他们签字后，我们对生产环境进行最终部署。

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.

现在，关于这些 CLOB 列：当使用 desc 和/或查询 dev 数据库的 all_tab_columns 视图时，CLOB 显示的数据长度为 4,000。但是，在临时数据库和生产数据库中，与开发等效的 CLOB 列的数据长度是奇数，例如 86。我已经搜索了所有可能的解决方案，以了解这是如何发生的。我什至尝试添加一个新的 CLOB(86) 列，认为它会像 VARCHAR2 一样工作，但 Oracle 只是吐出一个错误。

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.

DBA 会不会搞砸了一些事情？这还有什么可担心的吗？似乎没有任何事情因此而中断，但我只是喜欢元数据在所有环境中都相同。

回答by ik_zelf

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.

首先，我 - 作为一名 dba - 看到你和 dba 之间缺乏合作感到遗憾。我们都需要合作才能成功。Clob 数据长度可以小于 4000 字节。

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?

哪里发现一个clob长度不能小于4000？

回答by Shannon Severance

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.

DATA_LENGTH 存储列的行内将占用的最大字节数。如果 CLOB 可以按行存储，那么最大为 4000。LOBS 永远不会占用超过 4000 个字节。如果行内存储被禁用，那么 LOB 将只存储它需要查找 LOB 数据的指针信息，该信息远小于 4000 字节。

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

编辑，在 *_LOBS 视图上添加信息

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

使用 [DBA|ALL|USER]_LOBS 视图查看行外存储设置中定义的：

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

编辑 2，一些参考

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

见LOB存储在Oracle数据库应用程序开发人员指南-大型对象的详细信息，确定LOB存储，尤其是第三个需要注意的是什么是可以改变的谈话：

Note:

Only some storage parameters can be modified. For example, you can use the ALTER TABLE ... MODIFY LOBstatement to change RETENTION, PCTVERSION, CACHEor NO CACHE LOGGINGor NO LOGGING, and the STORAGEclause.

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.

笔记：

只能修改部分存储参数。例如，您可以使用ALTER TABLE ... MODIFY LOB语句更改RETENTION、 PCTVERSION、CACHE或NO CACHE LOGGING或NO LOGGING和STORAGE子句。

您还可以使用 ALTER TABLE ... MOVE 语句更改 TABLESPACE。

但是，一旦创建了表，就不能更改 CHUNK 大小，或者 ENABLE 或 DISABLE STORAGE IN ROW 设置。

Also, LOBs in Index Organized Tablessays:

此外，索引组织表中的 LOB说：

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.

默认情况下，在没有溢出段的情况下创建的索引组织表中的所有 LOB 将被存储在行外。换句话说，如果创建的索引组织表没有溢出段，则该表中的 LOB 的默认存储属性为 DISABLE STORAGE IN ROW。如果您强行尝试为此类 LOB 指定 ENABLE STORAGE IN ROW 子句，则 SQL 将引发错误。

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

这解释了为什么 jonearles 在索引组织表中创建 LOB 时没有在 data_length 列中看到 4,000。

回答by Jeffrey Kemp

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

CLOB 没有指定的长度。当您查询 ALL_TAB_COLUMNS 时，例如：

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.

您会注意到 data_length 始终为 4000，但这应该被忽略。

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.

CLOB 的最小大小为零 (0)，最大大小为 8 TB 到 128 TB，具体取决于数据库块大小。

回答by Jon Heller

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

正如 ik_zelf 和 Jeffrey Kemp 指出的那样，CLOB 可以存储少于 4000 个字节。

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.

但是为什么 CLOB data_lengths 不总是 4000？该数字实际上并不限制 CLOB，但您担心服务器上的元数据不同可能是正确的。您可能希望在所有服务器上的对象上运行 DBMS_METADATA.GET_DDL 并比较结果。

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

通过向索引组织表中添加 CLOB，我能够创建一个低 data_length。

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.

在 10.2.0.1.0 上，结果是 116。
在 11.2.0.1.0 上，结果是 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?

有人知道这里到底发生了什么吗？