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

存储架构 2018-02-14

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.

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;


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

Hello, buddy!

责编内容by:Hello, buddy! (源链)。感谢您的支持!


Indexing In Oracle 12c When Extended String Length... Extended string lengths can be very beneficial but they can also be an impediment to index and constraint creation. Read on to see what the proble...
The Secret to Choosing the Oracle Database Certifi... Have you tried navigating the Oracle Certification labyrinth lately? If so, you may have come away with your head spinning! While there are many offer...
oracle的增量检查点与block buffer 增量检查点 首先本文不会作过多的概念描述,对于增量检查点机制,其实在任何关系型数据库里都会存在。从事务的持久性角度来看,他的目的也是显而易见的,即保证数据块的正常刷新以及崩溃恢复,那么检查点其实也就是对数据块刷新情况的一个位点记录,至于通过什么形式记录,不同的数据库各有差异。因此,要想实现日志预...
Oracle表空间SYSAUX使用率很高解决案例 本实例主要针对Oracle表空间饱满问题处理方法做个步骤分享。 一、告警信息 收到zabbix告警信息,表空间 SYSAUX 使用率>95%%,系统表空间sysaux使用率超过了95%。 07806a1f2d82f6f87bf5223975352abc 二、处理步...
Oracle 关系型分布式内存数据库 内存计算那点事 迄今为止,内存还是我们目前能用到的最快的存储设备,把数据尽可能放进内存成为各种应用提高数据访问性能的最有效途径。对于很多关键业务系统而言,内存又是一种“挥发性”的和大小非常有限的存储设备,如何在保证性能的同时使数据能持久化,如何把有限的内存投入到无限的待处理数据上是程序设计...