SQL*Plus COPY 命令遷移 LONG 欄位資料表：繞過 ORA-00997 限制

Oracle 8i 前大量使用 LONG/LONG RAW 儲存大型文本/二進制資料。遷移此類表時，CREATE TABLE AS SELECT (CTAS) 會報 ORA-00997: illegal use of LONG datatype。SQL*Plus 內建 COPY 命令雖屬舊功能（8.0 凍結、不支援 LOB/新型別），但對 LONG 遷移仍是最輕量、無需導出導入的單指令方案。

問題重現

SQL> CREATE TABLE my_views AS SELECT * FROM user_views;
ERROR at line 2:
ORA-00997: illegal use of LONG datatype
-- user_views.TEXT 為 LONG 型別

COPY 命令語法

COPY FROM  TO  
  CREATE  
  USING ;

-- 參數說明
-- FROM/TO: 連接字串，省略時使用當前連接
-- CREATE/REPLACE/APPEND/INSERT: 目標表動作
-- USING: 查詢語句，支援 WHERE、JOIN

實戰步驟

-- 1. 設定 LONG 顯示長度（避免截斷）
SET LONG 100000
SET LONGCHUNKSIZE 10000
SET ARRAYSIZE 50
SET COPYCOMMIT 0

-- 2. 執行 COPY
SQL> COPY FROM CREATE my_views USING SELECT * FROM user_views;

Array fetch/bind size is 50
Will commit when done. (copycommit is 0)
Maximum long size is 100000. (long is 100000)
Table MY_VIEWS created.
1 rows selected from DEFAULT HOST connection.
1 rows inserted into MY_VIEWS.
1 rows committed into MY_VIEWS at DEFAULT HOST connection.

-- 3. 驗證結構
SQL> DESC my_views;
Name             Null?    Type
---------------- -------- --------------------
VIEW_NAME        NOT NULL VARCHAR2(30)
TEXT_LENGTH               NUMBER(38)
TEXT                        LONG
TYPE_TEXT_LENGTH            NUMBER(38)
TYPE_TEXT                   VARCHAR2(4000)
OID_TEXT_LENGTH             NUMBER(38)
OID_TEXT                    VARCHAR2(4000)
VIEW_TYPE_OWNER             VARCHAR2(30)
VIEW_TYPE                   VARCHAR2(30)
SUPERVIEW_NAME              VARCHAR2(30)

COPY 支援/不支援資料型別

替代方案對比

12c+ 推薦：LONG 轉 CLOB 永久修復

-- 1. 新增 CLOB 欄位
ALTER TABLE my_views ADD (text_clob CLOB);

-- 2. 更新轉換（分批 Commit 避免 Undo 爆滿）
DECLARE
  CURSOR c IS SELECT rowid, text FROM my_views WHERE text IS NOT NULL FOR UPDATE;
  l_clob CLOB;
BEGIN
  FOR r IN c LOOP
    l_clob := TO_LOB(r.text);
    UPDATE my_views SET text_clob = l_clob WHERE rowid = r.rowid;
    IF MOD(c%ROWCOUNT, 100) = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
END;
/

-- 3. 刪除舊 LONG 欄位、重命名
ALTER TABLE my_views DROP COLUMN text;
ALTER TABLE my_views RENAME COLUMN text_clob TO text;

COPY 是「搶險工具」，適合緊急遷移少量舊 LONG 表。長期治理應規劃 LONG → CLOB 全量轉型，徹底消除 ORA-00997 限制，獲得完整 LOB 功能與效能優化能力。