ORA-39083 ORA-00959
ORA
2023-09-14 08:59:39 时间
Failing sql is:
CREATE TABLE "TSALE"."B_SELL_CLASSIFY" ("SELL_CLASS_NO" VARCHAR2(50 BYTE) NOT NULL ENABLE, "SELL_CLASS_NAME" VARCHAR2(100 BYTE), "I
" VARCHAR2(36 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65
36 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
ORA-39083: Object type TABLE:"TSALE"."B_TRANS_ADDR" failed to create with error:
ORA-00959: tablespace SALES does not exist
Failing sql is:
快速解决IMP中的ORA-00959 用IMP向测试库导入DMP时，又遇到了ORA-00959表空间不存在的问题。
一般的解决方法： 1.先建表，在导入dmp时加ignore=y 的参数。 2.新建对应的表空间 3.使用expdp从新导出，在impdp时用REMAP_TABLESPACE 无论用那种方法都比较麻烦，都需要耗费一定的工作量。如果在oracle10g中，
采用重命名表空间的方法,可以快速解决这个问题。 SQL alter tablespace old_tbsname rename to new_tbs_name; 如当前用户默认的表空间为TEST,现在改为需要的表空间YWDBS
SQL alter tablespace TEST rename to YWDBS; /oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none Import: Release 10.2.0.4.0 - Production on Wed Jul 6 09:39:25 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by DEV, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DEVs objects into REPORTTEST
. importing DEVs objects into REPORTTEST
IMP-00017: following statement failed with ORACLE error 959:
...
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace YWDBS does not exist
Import terminated successfully with warnings. SQL alter tablespace TEST rename to YWDBS; 重命名表空间后，会在alert.log中看到如下信息：
Tablespace TEST is renamed to YWDBS.
Completed: alter tablespace TEST rename to YWDBS 重命名表空间后，dmp可以顺利导入
/oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none Import: Release 10.2.0.4.0 - Production on Wed Jul 6 10:05:08 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by DEV, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DEVs objects into REPORTTEST
. importing DEVs objects into REPORTTEST
. . importing table "PRPDRISK" 68 rows imported
Import terminated successfully without warnings. 为了管理上的规范，在导入成功后可以在将表空间名该回去。 IMP中的ORA-00959一般发生在有CLOB字段的表上。
创建表，提示ORA-00959:表空间MC_DATA不存在的解决 首先，先确认当前用户的表空间。 select username,default_tablespace, temporary_tablespace from dba_users where username=MCMIDYL; www.2cto.com 发现默认表空间是MC_DATA，但是MC_DATA已经删除。 只需要将默认表空间改为，用户中存在的表空间即可。 修改语句如下 ALTER USER 用户名 DEFAULT TABLESPACE 表空间名
expdp system/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB logfile=FALABELLA_AR_expdp_20111214.log status=10 parallel=1 CONTENT=ALL flashback_scn=612654934
CREATE TABLESPACE TBS_FALABELLA_AR LOGGING DATAFILE /u01/oradata/oracle/TBS_FALABELLA_AR01.DBF
CREATE USER FALABELLA_AR_CORE IDENTIFIED BY FALABELLA_AR_CORE QUOTA UNLIMITED ON TBS_FALABELLA_AR
CREATE USER FALABELLA_AR_CATA IDENTIFIED BY FALABELLA_AR_CATA QUOTA UNLIMITED ON TBS_FALABELLA_AR
CREATE USER FALABELLA_AR_CATB IDENTIFIED BY FALABELLA_AR_CATB QUOTA UNLIMITED ON TBS_FALABELLA_AR
CREATE USER FALABELLA_AR_PUB IDENTIFIED BY FALABELLA_AR_PUB QUOTA UNLIMITED ON TBS_FALABELLA_AR
impdp system/oracle@oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP LOGFILE=FALABELLA_AR_impdp_ALL_20111215.log SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB REMAP_TABLESPACE=TBS_FALABELLA:TBS_FALABELLA_AR CONTENT=ALL PARALLEL=1 STATUS=10 TABLE_EXISTS_ACTION=REPLACE TRANSFORM=segment_attributes:n:table
导入时发现源库和目标库的字符集不一样，导入的时候虽然完成了，但有一些字段在字符转换的时候报出字段长度不够。检查日志，发现很多如下错误：
于是想出一个办法，先单独导入元数据，然后查找日志，找出所有字段长度有问题的表，把相应的列长度改长：
impdp system/oracle@oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP LOGFILE=FALABELLA_AR_impdp_METADATA_ONLY_DISABLE_CONSTRAINT_20111215.log SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB REMAP_TABLESPACE=TBS_FALABELLA:TBS_FALABELLA_AR CONTENT=METADATA_ONLY PARALLEL=1 STATUS=10 TABLE_EXISTS_ACTION=REPLACE TRANSFORM=segment_attributes:n:table
ALTER TABLE FALABELLA_AR_PUB.RGLLEVVF MODIFY ( XNMB_NOVIO CHAR(30 BYTE), XAPP_NOVIO CHAR(30 BYTE), XAPM_NOVIO CHAR(30 BYTE), XNMB_NOVIA CHAR(30 BYTE), XAPP_NOVIA CHAR(30 BYTE), XAPM_NOVIA CHAR(30 BYTE));
ALTER TABLE FALABELLA_AR_CORE.FBL_ELECTRONIC_SHIPPING MODIFY(SENDER_NAME VARCHAR2(64 BYTE));
ALTER TABLE FALABELLA_AR_CATA.FBL_STOCK_N_SHIP_WAREHOUSE MODIFY ( CUT_OFF_TIME VARCHAR2(10 BYTE));
ALTER TABLE FALABELLA_AR_CATB.FBL_STOCK_N_SHIP_WAREHOUSE MODIFY ( CUT_OFF_TIME VARCHAR2(10 BYTE));
ALTER TABLE FALABELLA_AR_PUB.FBL_STOCK_N_SHIP_WAREHOUSE MODIFY( CUT_OFF_TIME VARCHAR2(10 BYTE));
ALTER TABLE FALABELLA_AR_CORE.FBL_ORDER_BANK_PROMOTION MODIFY (BANK_NAME VARCHAR2(30 BYTE));
ORA-31693: Table data object "FALABELLA_AR_CORE"."DCSPP_SHIPITEM_REL" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (FALABELLA_AR_CORE.DCSPP_SHRELTNSHP_F) violated - parent key not found
select ALTER TABLE ||OWNER||.||TABLE_NAME|| DISABLE CONSTRAINT ||constraint_name||; from dba_constraints where owner in(FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB) and CONSTRAINT_TYPE=R;
impdp system/oracle@oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP LOGFILE=FALABELLA_AR_impdp_DATA_ONLY_DISABLE_CONSTRAINT__20111215.log SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB REMAP_TABLESPACE=TBS_FALABELLA:TBS_FALABELLA_AR CONTENT=DATA_ONLY PARALLEL=1 STATUS=10 TRANSFORM=segment_attributes:n:table
select ALTER TABLE ||OWNER||.||TABLE_NAME|| ENABLE NOVALIDATE CONSTRAINT ||constraint_name||; from dba_constraints where owner in(FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB) and CONSTRAINT_TYPE=R;
快速解决IMP中的ORA-00959 用IMP向测试库导入DMP时，又遇到了ORA-00959表空间不存在的问题。
一般的解决方法： 1.先建表，在导入dmp时加ignore=y 的参数。 2.新建对应的表空间 3.使用expdp从新导出，在impdp时用REMAP_TABLESPACE 无论用那种方法都比较麻烦，都需要耗费一定的工作量。如果在oracle10g中，
采用重命名表空间的方法,可以快速解决这个问题。 SQL alter tablespace old_tbsname rename to new_tbs_name; 如当前用户默认的表空间为TEST,现在改为需要的表空间YWDBS
SQL alter tablespace TEST rename to YWDBS; /oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none Import: Release 10.2.0.4.0 - Production on Wed Jul 6 09:39:25 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by DEV, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DEVs objects into REPORTTEST
. importing DEVs objects into REPORTTEST
IMP-00017: following statement failed with ORACLE error 959:
...
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace YWDBS does not exist
Import terminated successfully with warnings. SQL alter tablespace TEST rename to YWDBS; 重命名表空间后，会在alert.log中看到如下信息：
Tablespace TEST is renamed to YWDBS.
Completed: alter tablespace TEST rename to YWDBS 重命名表空间后，dmp可以顺利导入
/oracle$imp TEST/TEST file=dev_bak_20110702.dmp tables=PRPDRISK statistics=none Import: Release 10.2.0.4.0 - Production on Wed Jul 6 10:05:08 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by DEV, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing DEVs objects into REPORTTEST
. importing DEVs objects into REPORTTEST
. . importing table "PRPDRISK" 68 rows imported
Import terminated successfully without warnings. 为了管理上的规范，在导入成功后可以在将表空间名该回去。 IMP中的ORA-00959一般发生在有CLOB字段的表上。
创建表，提示ORA-00959:表空间MC_DATA不存在的解决 首先，先确认当前用户的表空间。 select username,default_tablespace, temporary_tablespace from dba_users where username=MCMIDYL; www.2cto.com 发现默认表空间是MC_DATA，但是MC_DATA已经删除。 只需要将默认表空间改为，用户中存在的表空间即可。 修改语句如下 ALTER USER 用户名 DEFAULT TABLESPACE 表空间名
expdp system/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB logfile=FALABELLA_AR_expdp_20111214.log status=10 parallel=1 CONTENT=ALL flashback_scn=612654934
CREATE TABLESPACE TBS_FALABELLA_AR LOGGING DATAFILE /u01/oradata/oracle/TBS_FALABELLA_AR01.DBF
CREATE USER FALABELLA_AR_CORE IDENTIFIED BY FALABELLA_AR_CORE QUOTA UNLIMITED ON TBS_FALABELLA_AR
CREATE USER FALABELLA_AR_CATA IDENTIFIED BY FALABELLA_AR_CATA QUOTA UNLIMITED ON TBS_FALABELLA_AR
CREATE USER FALABELLA_AR_CATB IDENTIFIED BY FALABELLA_AR_CATB QUOTA UNLIMITED ON TBS_FALABELLA_AR
CREATE USER FALABELLA_AR_PUB IDENTIFIED BY FALABELLA_AR_PUB QUOTA UNLIMITED ON TBS_FALABELLA_AR
impdp system/oracle@oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP LOGFILE=FALABELLA_AR_impdp_ALL_20111215.log SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB REMAP_TABLESPACE=TBS_FALABELLA:TBS_FALABELLA_AR CONTENT=ALL PARALLEL=1 STATUS=10 TABLE_EXISTS_ACTION=REPLACE TRANSFORM=segment_attributes:n:table
导入时发现源库和目标库的字符集不一样，导入的时候虽然完成了，但有一些字段在字符转换的时候报出字段长度不够。检查日志，发现很多如下错误：
于是想出一个办法，先单独导入元数据，然后查找日志，找出所有字段长度有问题的表，把相应的列长度改长：
impdp system/oracle@oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP LOGFILE=FALABELLA_AR_impdp_METADATA_ONLY_DISABLE_CONSTRAINT_20111215.log SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB REMAP_TABLESPACE=TBS_FALABELLA:TBS_FALABELLA_AR CONTENT=METADATA_ONLY PARALLEL=1 STATUS=10 TABLE_EXISTS_ACTION=REPLACE TRANSFORM=segment_attributes:n:table
ALTER TABLE FALABELLA_AR_PUB.RGLLEVVF MODIFY ( XNMB_NOVIO CHAR(30 BYTE), XAPP_NOVIO CHAR(30 BYTE), XAPM_NOVIO CHAR(30 BYTE), XNMB_NOVIA CHAR(30 BYTE), XAPP_NOVIA CHAR(30 BYTE), XAPM_NOVIA CHAR(30 BYTE));
ALTER TABLE FALABELLA_AR_CORE.FBL_ELECTRONIC_SHIPPING MODIFY(SENDER_NAME VARCHAR2(64 BYTE));
ALTER TABLE FALABELLA_AR_CATA.FBL_STOCK_N_SHIP_WAREHOUSE MODIFY ( CUT_OFF_TIME VARCHAR2(10 BYTE));
ALTER TABLE FALABELLA_AR_CATB.FBL_STOCK_N_SHIP_WAREHOUSE MODIFY ( CUT_OFF_TIME VARCHAR2(10 BYTE));
ALTER TABLE FALABELLA_AR_PUB.FBL_STOCK_N_SHIP_WAREHOUSE MODIFY( CUT_OFF_TIME VARCHAR2(10 BYTE));
ALTER TABLE FALABELLA_AR_CORE.FBL_ORDER_BANK_PROMOTION MODIFY (BANK_NAME VARCHAR2(30 BYTE));
然后再单独导入数据，但单独导入数据时由于先后关系，会检查参照完整性，于是先DISABLE所有的参照完整性，再导入数据，等导入完成后，再启用所有的参照完整性约束。
ORA-31693: Table data object "FALABELLA_AR_CORE"."DCSPP_SHIPITEM_REL" failed to load/unload and is being skipped due to error:
ORA-02291: integrity constraint (FALABELLA_AR_CORE.DCSPP_SHRELTNSHP_F) violated - parent key not found
select ALTER TABLE ||OWNER||.||TABLE_NAME|| DISABLE CONSTRAINT ||constraint_name||; from dba_constraints where owner in(FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB) and CONSTRAINT_TYPE=R;
impdp system/oracle@oracle DIRECTORY=DATA_PUMP_DIR DUMPFILE=FALABELLA_AR_20111214.DMP LOGFILE=FALABELLA_AR_impdp_DATA_ONLY_DISABLE_CONSTRAINT__20111215.log SCHEMAS=FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB REMAP_TABLESPACE=TBS_FALABELLA:TBS_FALABELLA_AR CONTENT=DATA_ONLY PARALLEL=1 STATUS=10 TRANSFORM=segment_attributes:n:table
select ALTER TABLE ||OWNER||.||TABLE_NAME|| ENABLE NOVALIDATE CONSTRAINT ||constraint_name||; from dba_constraints where owner in(FALABELLA_AR_CORE,FALABELLA_AR_CATA,FALABELLA_AR_CATB,FALABELLA_AR_PUB) and CONSTRAINT_TYPE=R;
相关文章
- ORA-24803: illegal parameter value in lob read function ORACLE 报错 故障修复 远程处理
- ORA-28005: invalid logon flags ORACLE 报错 故障修复 远程处理
- ORA-32418: COMMIT SCN keyword only appears in CREATE MATERIALIZED VIEW LOG statement ORACLE 报错 故障修复 远程处理
- ORA-32609: missing REFERENCE keyword in MODEL clause ORACLE 报错 故障修复 远程处理
- ORA-39106: Worker process string failed during startup. Worker error: ORACLE 报错 故障修复 远程处理
- ORA-39135: File “string” is already a member of this dump set ORACLE 报错 故障修复 远程处理
- ORA-40107: operation requires string option to be installed ORACLE 报错 故障修复 远程处理
- ORA-51106: check failed to complete due to an error. See error below ORACLE 报错 故障修复 远程处理
- ORA-54607: CREATE_PC: error fetching data from input points table ORACLE 报错 故障修复 远程处理
- ORA-56934: database property DST_UPGRADE_STATE for datapump job is in an inconsistent state ORACLE 报错 故障修复 远程处理
- ORA-63002: a data file copy that is not current ORACLE 报错 故障修复 远程处理
- ORA-01683: unable to extend index string.string partition string by string in tablespace string ORACLE 报错 故障修复 远程处理
- ORA-12045: invalid ALTER MATERIALIZED VIEW LOG option ORACLE 报错 故障修复 远程处理
- ORA-15109: conflicting protection attributes specified ORACLE 报错 故障修复 远程处理
- ORA-19032: Expected XML tag string got string ORACLE 报错 故障修复 远程处理