Oracle 10g:CLOB数据长度可以小于4,000吗? [英] 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.

现在，关于以下CLOB列:使用desc和/或查询dev_all数据库的dev_tab_columns视图时，CLOB的数据长度为4,000.但是，在登台和生产数据库中，等效于dev的CLOB列的数据长度是奇数，例如86.我一直在寻找所有可能的解决方案，以了解这是如何实现的.我什至尝试添加一个新的CLOB(86)列，认为它可以像对VARCHAR2一样工作，但是Oracle只是吐出了一个错误.

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.

DBA会破坏一些东西吗?这甚至有什么可担心的吗?因此，似乎一切都没有中断，但我只是希望元数据在所有环境中都相同.

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.

推荐答案

DATA_LENGTH存储将在列的行中占用的最大字节数.如果CLOB可以存储在行中，则最大值为4000.LOBS永远不会占用超过4000字节.如果禁用了行内存储，则LOB将仅存储查找LOB数据所需的指针信息，该信息远少于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

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

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

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

编辑2，一些参考文献

请参见中的 LOB存储 《 Oracle数据库应用程序开发人员指南-大对象》 适用于有关定义LOB存储的更多信息，特别是关于可以更改的内容的第三个注释:

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:

注意:

仅某些存储参数可以修改.例如，你 可以使用ALTER TABLE ... MODIFY LOB语句更改RETENTION， PCTVERSION，CACHE或NO CACHE LOGGING或NO LOGGING，以及STORAGE 条款.

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.

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

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

但是，一旦创建了表，就无法更改CHUNK 大小或启用或禁用行中存储"设置.

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

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

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

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.

这说明了为什么jonearles在索引组织表中创建LOB时，在data_length列中看不到4,000.

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

这篇关于Oracle 10g:CLOB数据长度可以小于4,000吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！