ora-00997 非法使用LONG数据类型

今天对一张表进行迁移的时候发现报以下错误,参考飞总处理过程，原文链接：http://www.xifenfei.com/2012/07/%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>  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24486203/viewspace-1805605/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24486203/viewspace-1805605/