我在一个库中将一个表空间的表和数据导出,然后导入到另一个库的一个表空间中,但是出现了十几个ORA-00936错误:
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_ELABEL_RSP_32631" ("I_TABID" NUMBER(*,0), "I_SRN" NUMBER(*,0), "I_RN" NUMBER(*,0), "I_PN" NUMBER(*,0), "I_IN" NUMBER(*,0), "I_OT" NUMBER(*,0), "SV_LN" VARCHAR2(128 BYTE) DEFAULT , "I_OP" NUMBER(*,0) DEFAULT , "SV_BT" VARCHAR2(128 BYTE) DEFAULT , "SV_BC" VARCHAR2(128 BYTE) DEFAULT , "SV_ITEM" VARCHAR2(128 BYTE) DEFAULT , "SV_DESC" VARCHAR2(128 BYTE) DEFAULT , "S
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32322_2979" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0), "I_DATAID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32341_7000" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32386_7000" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32394_7012" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS 报错的这些表是临时表,我很奇怪所有的错误都是default后面缺少了默认值,而实际上我检查了存储过程中动态建临时表的语句,都是DEFAULT -1 NOT NULL ENABLE,确定没有问题啊。
况且,如果说我导出的源环境的这些表有问题,那么这些表应该根本建不成功,也不应该会被导出来,这样有怎么会碰到导入的问题呢?另外由于我们做了一个处理会定时删除这些我们存储过程中生成的临时表,因此当时报错的这些表实际上我现在在源导出环境上已经是查不到了,死无对证,陷入僵局了,有没有人碰到过类似问题?
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_ELABEL_RSP_32631" ("I_TABID" NUMBER(*,0), "I_SRN" NUMBER(*,0), "I_RN" NUMBER(*,0), "I_PN" NUMBER(*,0), "I_IN" NUMBER(*,0), "I_OT" NUMBER(*,0), "SV_LN" VARCHAR2(128 BYTE) DEFAULT , "I_OP" NUMBER(*,0) DEFAULT , "SV_BT" VARCHAR2(128 BYTE) DEFAULT , "SV_BC" VARCHAR2(128 BYTE) DEFAULT , "SV_ITEM" VARCHAR2(128 BYTE) DEFAULT , "SV_DESC" VARCHAR2(128 BYTE) DEFAULT , "S
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32322_2979" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0), "I_DATAID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32341_7000" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32386_7000" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS
ORA-39083: Object type TABLE failed to create with error:
ORA-00936: missing expression
Failing sql is:
CREATE GLOBAL TEMPORARY TABLE "CGP_1"."TBL_OPEROBJ_32394_7012" ("I_NEID" NUMBER(*,0) DEFAULT NOT NULL ENABLE, "I_CPUID" NUMBER(*,0), "I_PID" NUMBER(*,0)) ON COMMIT PRESERVE ROWS 报错的这些表是临时表,我很奇怪所有的错误都是default后面缺少了默认值,而实际上我检查了存储过程中动态建临时表的语句,都是DEFAULT -1 NOT NULL ENABLE,确定没有问题啊。
况且,如果说我导出的源环境的这些表有问题,那么这些表应该根本建不成功,也不应该会被导出来,这样有怎么会碰到导入的问题呢?另外由于我们做了一个处理会定时删除这些我们存储过程中生成的临时表,因此当时报错的这些表实际上我现在在源导出环境上已经是查不到了,死无对证,陷入僵局了,有没有人碰到过类似问题?
咋看咋联想到BUG。
我还不知道这是oracle的约束还是bug,个人感觉更想bug,SR上还没给我确认。
SQL> select count(1) from dba_objects where OBJECT_NAME like 'TBL_ELABEL%' and owner = 'CGP_1'; COUNT(1)
----------
137782. 使用下述命令执行CGP_1的导出
expdp cgp/cgp directory=BKMEXPIMPDIR dumpfile=CGP_1.dmp logfile=CGP_1.expdp.log schemas=CGP_1 REUSE_DUMPFILES=Y exclude=PROCEDURE,FUNCTION;在执行导出的同时,频繁执行一个存储过程,该存储过程中每隔10秒钟就删除一个TBL_ELABEL_xxx的临时表,并记下来删除了哪些表。
被删除的表如下:
SQL> select * from TBL_RMVTBL_LIST;SV_TBL_NAME
----------------------------------------------------------------
TBL_ELABEL_LIST_10056
TBL_ELABEL_LIST_10081
TBL_ELABEL_LIST_10494
TBL_ELABEL_LIST_105
...
TBL_ELABEL_LIST_12132
TBL_ELABEL_LIST_12285
TBL_ELABEL_LIST_1230260 rows selected.删除后TBL_ELABEL_临时表还有13718个
SQL> select count(1) from dba_objects where OBJECT_NAME like 'TBL_ELABEL_%' and owner = 'CGP_1';
COUNT(1)
----------
13718