解决ORACLE导入imp/exp导入表空间名不同的办法，以及一些其他错误填坑

@ORACLE IMP/EXP使用，两个库表空间不同时的解决办法

ORACLE IMP/EXP使用，两个库表空间不同时的解决办法，以及一些其他错误填坑

导入导出表空间不一致报错

报错样例

Export file created by EXPORT:V19.00.00 via conventional path

Warning: the objects were exported by HBS_DCORE_BASE2, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import 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 imported
IMP-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 encountered
ORA-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 imported
IMP-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 encountered
ORA-01031: insufficient privileges
IMP-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 encountered
ORA-01031: insufficient privileges
IMP-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 encountered
ORA-01031: insufficient privileges
IMP-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 encountered
ORA-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 错误 1950
ORA-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