@ORACLE IMP/EXP使用，两个库表空间不同时的解决办法
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#@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
