ora-00997 非法使用LONG資料型別

今天對一張表進行遷移的時候發現報以下錯誤,參考飛總處理過程，原文連結：%E4%BD%BF%E7%94%A8copy%E5%AE%9E%E7%8E%B0long%E7%B1%BB%E5%9E%8B%E8%BD%AC%E7%A7%BB%E8%A1%A8%E7%A9%BA%E9%97%B4.html

SQL> alter table PMLBIN move tablespace ZNJK;
alter table PMLBIN move tablespace ZNJK
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

生成ddl語句

SQL> select dbms_metadata.get_ddl('TABLE','PMLBIN','ZNJK') from dual;

  CREATE TABLE "ZNJK"."PMLBIN"
   (     "LBIN" NUMBER(*,0) NOT NULL ENABLE,
     "NUMR" NUMBER(*,0) NOT NULL ENABLE,
     "BDAT" LONG RAW
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLA
SH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

修改後

  CREATE TABLE "ZNJK"."PMLBINBAK"

   (     "LBIN" NUMBER(*,0 ) NOT NULL ENABLE ,

     "NUMR" NUMBER(*,0 ) NOT NULL ENABLE ,

     "BDAT" LONG RAW

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS

1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "ZNJK"

;

SQL> copy from znjk/xxx@xyxdb INSERT PMLBINBAK using select * from PMLBIN;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 1000. (long is 1000)
   47 rows selected from znjk@xyxdb.
   47 rows inserted into PMLBINBAK.
   47 rows committed into PMLBINBAK at DEFAULT HOST connection.

SQL>

SQL> alter table PMLBIN rename to PMLBIN_OLD;

Table altered.

SQL> alter table PMLBINBAK rename to PMLBIN;

Table altere

SQL> select table_name,tablespace_name from user_tables t where t.table_name='PMLBIN';

PMLBIN                      ZNJK

SQL> drop table PMLBIN_OLD purge;

Table dropped.

SQL>