本文共 6753 字，大约阅读时间需要 22 分钟。
@ORACLE IMP/EXP使用，两个库表空间不同时的解决办法
报错样例
Export file created by EXPORT:V19.00.00 via conventional pathWarning: the objects were exported by HBS_DCORE_BASE2, not by youimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses AL32UTF8 character set (possible charset conversion)export client uses ZHS16GBK character set (possible charset conversion). . importing table "AI_GLM" 15 rows imported. . importing table "AUTH_CONF_INFO" 749 rows imported. . importing table "AUTH_INTERFACE_INFO" 0 rows imported. . importing table "BP_BANK" 4 rows imported. . importing table "BP_CA_CAL" 86 rows imported. . importing table "BP_CA_HCK" 0 rows imported. . importing table "BP_CA_HOL_DETAIL" 3 rows imported. . importing table "BP_CA_USH" 0 rows imported. . importing table "BP_CA_WND" 0 rows imported. . importing table "BP_CR_CRY" 10 rows imported. . importing table "BP_CT_CIT" 344 rows imported. . importing table "BP_CT_CNT" 253 rows imported. . importing table "BP_DATE" 1 rows imported. . importing table "BP_EXCEL_UPLOAD_HIST" 0 rows imported. . importing table "BP_FEE" 0 rows imported. . importing table "BP_FEE_BAS" 10 rows imported. . importing table "BP_FEE_STD" 227 rows imported. . importing table "BP_ORG" 4 rows importedIMP-00017: following statement failed with ORACLE error 959: "CREATE TABLE "BP_PARM" ("PARM_TYP" NVARCHAR2(10) NOT NULL ENABLE, "PARM_CD"" " NVARCHAR2(40) NOT NULL ENABLE, "EFF_DATE" DATE NOT NULL ENABLE, "EXP_DATE"" " DATE NOT NULL ENABLE, "EDESC" NVARCHAR2(60) NOT NULL ENABLE, "CDESC" NVARC" "HAR2(60) NOT NULL ENABLE, "VAL" NCLOB, "CREATE_TIME" DATE, "MODIFY_TIME" DA" "TE, "UPD_BR" NUMBER(20, 0), "UPD_TLR" NVARCHAR2(20), "TS" DATE) PCTFREE 10" " PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072 NEXT 1048576 MIN" "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "HB" "S_DCORE_BASE2_DATA" LOGGING NOCOMPRESS LOB ("VAL") STORE AS SECUREFILE (TA" "BLESPACE "HBS_DCORE_BASE2_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION " "AUTO NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEX" "T 1048576 MINEXTENTS 1 BUFFER_POOL DEFAULT))"IMP-00003: ORACLE error 959 encounteredORA-00959: tablespace 'HBS_DCORE_BASE2_DATA' does not exist. . importing table "BP_PRCD" 594 rows imported. . importing table "BP_PRTY" 0 rows imported. . importing table "BP_PR_GRP" 8 rows imported. . importing table "BP_PR_GRP_LA" 23 rows imported. . importing table "BP_PR_TTR_OL" 2 rows imported. . importing table "BP_UNIT" 2 rows imported. . importing table "BP_USER" 27 rows imported. . importing table "KONT_APP_SYS_INFO" 4 rows importedIMP-00017: following statement failed with ORACLE error 959: "CREATE TABLE "KONT_CHANNEL" ("CHANNEL_ID" NVARCHAR2(32) NOT NULL ENABLE, "C" "HANNEL_NAME" NVARCHAR2(200), "APP_KEY" NVARCHAR2(16) NOT NULL ENABLE, "APP_" "SECRET" NVARCHAR2(32), "TLR" NVARCHAR2(20), "ACCESS_TOKEN" NVARCHAR2(32), "" "ENABLE" NVARCHAR2(1), "ENCRYPTION_SWITCH" NVARCHAR2(1), "TOKEN_TIMEOUT_SWIT" "CH" NVARCHAR2(1), "AUTHORITY_SWITCH" NVARCHAR2(100), "SYSTEM_PUBLICKEY" NVA" "RCHAR2(500), "SYSTEM_PRIVATEKEY" NCLOB, "CHANNEL_PUBLICKEY" NVARCHAR2(500)," " "CHANNEL_PRIVATEKEY" NCLOB, "MARK" NCLOB, "CREATE_TIME" DATE, "MODIFY_TIME" "" DATE, "TS" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(I" "NITIAL 131072 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFE" "R_POOL DEFAULT) TABLESPACE "HBS_DCORE_BASE2_DATA" LOGGING NOCOMPRESS LOB ("" "CHANNEL_PRIVATEKEY") STORE AS SECUREFILE (TABLESPACE "HBS_DCORE_BASE2_DATA" "" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION AUTO NOCACHE LOGGING NOCOMPRE" "SS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 BUFFER_" "POOL DEFAULT)) LOB ("MARK") STORE AS SECUREFILE (TABLESPACE "HBS_DCORE_ 解决办法：在导出exp时先重命名导出数据的表空间，之后再重新导出dmp文件
**重命名表空间和还原表空间需要再dba权限下执行**需要使用数据库服务器oracle用户，在$ORACLE_HOME/bin下执行，则进入dba权限用户./sqlplus / as sysdba重命名表空间alter tablespace HBS_DCORE_XXX_DATA rename to HBS_DATA;导出数据(注意：该代码不需要使用sqlplus，直接登录数据库服务器oracle用户执行即可)exp HBS_DCORE_XXX/HBS_DCORE_XXX#2021@core001 file=xxxnew.dmp log=xxx_exp.log statistics=none还原表空间名字alter tablespace HBS_DATA rename to HBS_DCORE_XXX_DATA ;之后使用导出的dmp导入新库即可
若imp导入出现下面报错则表明用户没有操作当前表空间的权限，需要进行授权
报错样例1
IMP-00003: ORACLE error 1031 encounteredORA-01031: insufficient privilegesIMP-00017: following statement failed with ORACLE error 1031:""CREATE TRIGGER "HBS_DCORE_AML".T_AML_TEL_CODE_TRI"" BEFORE INSERT ON T_AML_TEL_CODE"" FOR EACH ROW"" BEGIN"" SELECT SEQ_T_AML_TEL_CODE.nextval INTO :NEW.ID FROM DUAL; " END;"IMP-00003: ORACLE error 1031 encounteredORA-01031: insufficient privilegesIMP-00017: following statement failed with ORACLE error 1031:""CREATE TRIGGER "HBS_DCORE_AML".NBC_BLACKLIST_TRI"" BEFORE INSERT ON NBC_BLACKLIST"" FOR EACH ROW"" BEGIN"" SELECT SEQ_NBC_BLACKLIST.nextval INTO :NEW.ID FROM DUAL; " END;"IMP-00003: ORACLE error 1031 encounteredORA-01031: insufficient privilegesIMP-00017: following statement failed with ORACLE error 1031:""CREATE TRIGGER "HBS_DCORE_AML".T_XXX_SMS_COLLECTION_TRI"" BEFORE INSERT ON T_XXX_SMS_COLLECTION "" FOR EACH ROW"" BEGIN"" SELECT SEQ_T_AML_SMS_COLLECTION.nextval INTO :NEW.ID FROM DUAL; " END;"IMP-00003: ORACLE error 1031 encounteredORA-01031: insufficient privileges
报错样例2
IMP-00017: 由于 ORACLE 错误 1950, 以下语句失败: "CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) P" "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIST" "S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING NOCOM" "PRESS"IMP-00003: 遇到 ORACLE 错误 1950ORA-01950: 对表空间 'EXAMPLE' 无权限 授权代码
授权需要再dba权限下执行需要使用数据库服务器oracle用户，在$ORACLE_HOME/bin下执行，则进入dba权限用户./sqlplus / as sysdba登录后grant connect ,resource to XXX_DCORE_XXX; alter user XXX_DCORE_XXX quota unlimited on XXX_DATA;
imp导入样例
导入代码需要使用数据库服务器oracle用户执行，不需要登录sqlplus如果报找不到指令则去$ORACLE_HOME/bin下执行./imp 账号/密码@实例名 file=/home/oracle/data_f/xxxnew.dmp full=y ignore=y log=/home/oracle/data_f/xxxnew_imp.log
转载地址：http://mzji.baihongyu.com/