lob欄位型別轉換ora-22858

blob欄位預設值為null，需要修改blob欄位的預設值為empty_blob()

SQL> desc t_lob001;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER

CONTENT BLOB

SQL> alter table t_lob001 modify(content blob default empty_blob());

alter table t_lob001 modify(content blob default empty_blob())

*

ERROR at line 1:

ORA-22858: invalid alteration of datatype

上述sql語句由於進行了blob到blob的轉換，出現了無效的資料型別ora-22858

同樣非lob型別轉換為lob型別同樣也不允許。

SQL> alter table t_lob001 add name varchar2(1000);

Table altered.

SQL> alter table t_lob001 modify(name blob default empty_blob());

alter table t_lob001 modify(name blob default empty_blob())

*

ERROR at line 1:

ORA-22858: invalid alteration of datatypeSQL> desc t_lob001;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER

CONTENT BLOB

由於修改預設值，可以直接採取alter table t_lob001 modify( content default empty_blob())就可以了。

分析一下這個錯誤的產生，將lob欄位轉換為別的資料型別，或者將別的資料型別轉換為lob型別時，oracle是不允許的。

[oracle@server127 ~]$ oerr ora 22858

22858, 00000, "invalid alteration of datatype"

// *Cause: An attempt was made to modify the column type to object, REF,

// nested table, VARRAY or LOB type.

// *Action: Create a new column of the desired type and copy the current

// column data to the new type using the appropriate type

// constructor.

如果需要進行轉換，可以參照線上重定義來進行轉換欄位型別。

接下來分析下lob的預設值null和empty_blob()的區別，null值由於是未知， oracle不會為其分配儲存空間，

而empty_blob（）僅僅表示該lob欄位為空值，oracle還是會為其分配lob欄位段頭的一些儲存單位。

SQL> alter table t_lob001 modify(content default empty_blob());

Table altered.

SQL> alter table t_lob001 add(article blob default null);

Table altered.

SQL> desc t_lob001;

Name Null? Type

----------------------------------------- -------- ----------------------------

ID NUMBER

CONTENT BLOB

NAME VARCHAR2(1000)

ARTICLE BLOB

SQL> insert into t_lob001(id,name) values(1,'xiaoyu');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_lob.getlength(content) content_length,dbms_lob.getlength(article) article_length from t_lob001;

CONTENT_LENGTH ARTICLE_LENGTH

-------------- --------------

0

下面的測試更能說明empty_blob()僅僅只是一個空的lob欄位，並不是null未知，而且會為其分配相應的lob欄位段頭的儲存位置。

SQL> select id from t_lob001 where content is null;

no rows selected

SQL> select id from t_lob001 where article is null;

ID

----------

1

關於儲存空間null和empty_blob()測試：

SQL> declare

2 begin

3 for i in 1..1000 loop

4 insert into t_lob001 values(1,null,'xiaoyu',null);

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> analyze table t_lob001 compute statistics;

Table analyzed.

SQL> select blocks from user_tables where table_name='T_LOB001';

BLOCKS

----------

5

SQL> truncate table t_lob001;

Table truncated.

SQL> declare

2 begin

3 for i in 1..1000 loop

4 insert into t_lob001 values(1,empty_blob(),'xiaoyu',null);

5 end loop;

6 commit;

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> analyze table t_lob001 compute statistics;

Table analyzed.

SQL> select blocks from user_tables where table_name='T_LOB001';

BLOCKS

----------

13

可以看出empty_blob()儲存空間佔用了較多的儲存空間。

[@more@]