LONG型別遷移到LOB型別（一）

LONG型別由於過多的限制，Oracle很早就推薦使用LOB型別進行替代，在Oracle的官方文件中給出了幾種將LONG型別遷移到LOB型別的方法，這裡簡單討論一下。

這一篇討論利用TO_LOB進行遷移。

 

 

官方文件給出的方法如下：

SQL> CREATE TABLE T
  2  (ID NUMBER, 
  3  CONTENTS LONG);

Table created.

SQL> INSERT INTO T
  2  SELECT ROWNUM, OWNER || OBJECT_NAME || OBJECT_TYPE
  3  FROM ALL_OBJECTS;

75327 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE T_NEW
  2  (ID NUMBER,
  3  CONTENTS CLOB);

Table created.

SQL> INSERT INTO T_NEW
  2  SELECT ID, TO_LOB(CONTENTS)
  3  FROM T;

75327 rows created.

SQL> COMMIT;

Commit complete.

SQL> DROP TABLE T PURGE;

Table dropped.

SQL> RENAME T_NEW TO T;

Table renamed.

這種方法需要刪除原表，先不說是否會造成業務停頓，光是原表涉及的索引、約束、許可權等重建就已經很麻煩了。

其實這個方法可以改進為：

SQL> DROP TABLE T PURGE;

Table dropped.

SQL> CREATE TABLE T
  2  (ID NUMBER, 
  3  CONTENTS LONG);

Table created.

SQL> INSERT INTO T
  2  SELECT ROWNUM, OWNER || OBJECT_NAME || OBJECT_TYPE
  3  FROM ALL_OBJECTS;

75327 rows created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE T
  2  ADD (CONTENTS_NEW CLOB);

Table altered.

SQL> UPDATE T        
  2  SET CONTENTS_NEW = TO_LOB(CONTENTS);
SET CONTENTS_NEW = TO_LOB(CONTENTS)
                   *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected - got LONG

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

Table altered.

SQL> UPDATE T T1
  2  SET CONTENTS_NEW =
  3  (SELECT TO_LOB(CONTENTS)
  4  FROM T T2
  5  WHERE T1.ID = T2.ID);

75327 rows updated.

SQL> ALTER TABLE T DROP COLUMN CONTENTS;

Table altered.

SQL> ALTER TABLE T RENAME COLUMN CONTENTS_NEW TO CONTENTS;

Table altered.

雖然Oracle不支援在UPDATE語句的時候直接使用TO_LOB進行LONG型別的轉換，但是可以利用自關聯的方式繞過Oracle的限制。

這種方法避免了表的重建，也就避免了索引、約束以及許可權等物件的重建。

不過LONG欄位刪除後，會留下很多的空閒空間分別在表的各個 BLOCK中，如果有必要收縮高水位線，可以考慮對錶再進行一次MOVE操作。