ITPub博客

首页 > 数据库 > Oracle > ora-00997 非法使用LONG数据类型

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

原创 Oracle 作者:shawnloong 时间:2015-09-21 22:07:08 0 删除 编辑
今天对一张表进行迁移的时候发现报以下错误,参考飞总处理过程,原文链接: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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORA-12012 job解决
请登录后发表评论 登录
全部评论

注册时间:2011-08-29

  • 博文量
    111
  • 访问量
    215999