通过数据泵导远程入数据的时候需要我们把,导入的数据文件上传到远程服务器具有读写权限的路径的慕夏,然后在通过数据泵impdb 导入文件。这种方式需要我们能够登录到远程服务器,但是有时候我们没有这种登录权SEO靠我限,只能通过普通的方式 exp/imp 导入导入了。
exp/imp方式导入导出缺点:导入导出的效率低,时间长;将数据库orcl数据库,指定用户pt6 的表 导出到SEO靠我E:\xxx\oracledata目录下。
exp pt6/cape@127.0.0.1:1521/orcl file=E:\xxx\oracledata\xxxx.dmp log=E:\xxx\oraSEO靠我cledata\xxxx.dmp.txt owner=pt6将数据库中system用户与sys用户的表导出
exp system/manager@localhost/TEST file=d:\daochSEO靠我u.dmp owner=(system,sys)3.导出指定的表
将数据库中的表table1 、table2导出exp system/manager@localhost/TEST file=d:\daoSEO靠我chu.dmp tables=(table1,table2)2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owSEO靠我ner=(system,sys)3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,tSEO靠我able2)4 将数据库中的表table1中的字段filed1以”00″打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1SEO靠我) query=\” where filed1like '00%'\”上面是常用的导出,对于压缩我不太在意,用winzip把dmp文件可以很好的压缩。不过在上面命令后面 加上 coSEO靠我mpress=y 就可以了
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp systemSEO靠我/manager@TEST ignore=y full=y file=d:\daochu.dmp log=d:\daoru.txt示例2
imp pt6/cape@10.216.60.130:1521/SEO靠我orcl file=E:\xxx\xxxxx\oracledata\xxx.dmp TABLESPACES=pt6 log=E:\xx\xx\oracledata\xxx.txt FULL=Y ignSEO靠我ore = yimp help=y1. 获取帮助
导入一个完整数据库 imp system/manager file=bible_db log=dible_db full=y ignorSEO靠我e=y 导入一个或一组指定用户所属的全部表、索引和其他对象imp system/manager file=seapark log=seapark fromuser=seapark
imSEO靠我p system/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)
将一个用户所属的数据导入另一个用户imp sysSEO靠我tem/manager file=tank log=tank fromuser=seapark touser=seapark_copy
imp system/manager file=tank log=SEO靠我tank fromuser=(seapark,amy) touser=(seapark1, amy1)
导入一个表 imp system/manager file=tank log=taSEO靠我nk fromuser=seapark TABLES=(a,b) 从多个文件导入 imp system/manager file=(paycheck_1,paycheSEO靠我ck_2,paycheck_3,paycheck_4) log=paycheck,filesize=1G full=y 使用参数文件imp system/manager parfilSEO靠我e=bible_tables.par
bible_tables.par参数文件:
#Import the sample tables used for the Oracle8i Database AdmiSEO靠我nistrator’s
#Bible.
fromuser=seapark touser=seapark_copy file=seapark log=seapark_import
增量导入(9i中已经取消) SEO靠我 imp system./manager inctype= RECTORE FULL=Y FILE=A如果需要导入到远程数据库中,在创SEO靠我建表空间过程中需要创建远程数据库的表空间,需要通过sqlplus 登录远程数据库;格式为 用户名/密码@//数据库服务器IP:端口/数据库名称
如果是dba用户不要忘记加上 as sysdba
systeSEO靠我m/cape@//10.216.60.130:1521/orcl然后再执行创建表空间、临时表空间、用户、权限之类的操作;
数据库数据导入
imp pt6/cape@10.216.60.130:1521/oSEO靠我rcl file=E:\AVICIT\changfei\oracledata\CF642DEV2_20230508V4.dmp TABLESPACES=pt6 log=E:\AVICIT\changfSEO靠我ei\oracledata\CF642DEV2_20230508V4_daoru.txt FULL=Y ignore = y@SEO靠我ORACLE IMP/EXP使用,两个库表空间不同时的解决办法
ORACLE IMP/EXP使用,两个库表空间不同时的解决办法,以及一些其他错误填坑
导入导出表空间不一致报错
报错样例Export fileSEO靠我 created by EXPORT:V19.00.00 via conventional pathWarning: the objects were exported by HBS_DCORE_BASEO靠我SE2, not by youimport done in US7ASCII character set and AL16UTF16 NCHAR character set imporSEO靠我t server uses AL32UTF8 character set (possible charset conversion) export client uses ZHS16GSEO靠我BK character set (possible charset conversion) . . importing table "AI_GLM" 15 rows importedSEO靠我 . . importing table "AUTH_CONF_INFO" 749 rows imported . . importing table "AUTH_INSEO靠我TERFACE_INFO" 0 rows imported . . importing table "BP_BANK" 4 rows imported . . impoSEO靠我rting table "BP_CA_CAL" 86 rows imported . . importing table "BP_CA_HCK" 0 rows imported SEO靠我 . . importing table "BP_CA_HOL_DETAIL" 3 rows imported . . importing table "BP_CA_USH" SEO靠我0 rows imported . . importing table "BP_CA_WND" 0 rows imported . . importing table SEO靠我"BP_CR_CRY" 10 rows imported . . importing table "BP_CT_CIT" 344 rows imported . . iSEO靠我mporting table "BP_CT_CNT" 253 rows imported . . importing table "BP_DATE" 1 rows imported SEO靠我 . . importing table "BP_EXCEL_UPLOAD_HIST" 0 rows imported . . importing table "BP_FESEO靠我E" 0 rows imported . . importing table "BP_FEE_BAS" 10 rows imported . . importing tSEO靠我able "BP_FEE_STD" 227 rows imported . . importing table "BP_ORG" 4 rows imported IMPSEO靠我-00017: following statement failed with ORACLE error 959:"CREATE TABLE "BP_PARM" ("PARM_TYP" NVARCHASEO靠我R2(10) NOT NULL ENABLE, "PARM_CD""" NVARCHAR2(40) NOT NULL ENABLE, "EFF_DATE" DATE NOT NULL ENABLE, SEO靠我"EXP_DATE""" DATE NOT NULL ENABLE, "EDESC" NVARCHAR2(60) NOT NULL ENABLE, "CDESC" NVARC""HAR2(60) NOSEO靠我T NULL ENABLE, "VAL" NCLOB, "CREATE_TIME" DATE, "MODIFY_TIME" DA""TE, "UPD_BR" NUMBER(20, 0), "UPD_TSEO靠我LR" NVARCHAR2(20), "TS" DATE) PCTFREE 10"" PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072SEO靠我 NEXT 1048576 MIN""EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "HB""S_DCSEO靠我ORE_BASE2_DATA" LOGGING NOCOMPRESS LOB ("VAL") STORE AS SECUREFILE (TA""BLESPACE "HBS_DCORE_BASE2_DASEO靠我TA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION ""AUTO NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STOSEO靠我RAGE(INITIAL 106496 NEX""T 1048576 MINEXTENTS 1 BUFFER_POOL DEFAULT))" IMP-00003: ORACLE errSEO靠我or 959 encountered ORA-00959: tablespace HBS_DCORE_BASE2_DATA does not exist . . impSEO靠我orting table "BP_PRCD" 594 rows imported . . importing table "BP_PRTY" 0 rows imported SEO靠我 . . importing table "BP_PR_GRP" 8 rows imported . . importing table "BP_PR_GRP_LA" 23 rowSEO靠我s imported . . importing table "BP_PR_TTR_OL" 2 rows imported . . importing table "BSEO靠我P_UNIT" 2 rows imported . . importing table "BP_USER" 27 rows imported . . importingSEO靠我 table "KONT_APP_SYS_INFO" 4 rows imported IMP-00017: following statement failed with ORACLESEO靠我 error 959:"CREATE TABLE "KONT_CHANNEL" ("CHANNEL_ID" NVARCHAR2(32) NOT NULL ENABLE, "C""HANNEL_NAMESEO靠我" NVARCHAR2(200), "APP_KEY" NVARCHAR2(16) NOT NULL ENABLE, "APP_""SECRET" NVARCHAR2(32), "TLR" NVARCSEO靠我HAR2(20), "ACCESS_TOKEN" NVARCHAR2(32), """ENABLE" NVARCHAR2(1), "ENCRYPTION_SWITCH" NVARCHAR2(1), "SEO靠我TOKEN_TIMEOUT_SWIT""CH" NVARCHAR2(1), "AUTHORITY_SWITCH" NVARCHAR2(100), "SYSTEM_PUBLICKEY" NVA""RCHSEO靠我AR2(500), "SYSTEM_PRIVATEKEY" NCLOB, "CHANNEL_PUBLICKEY" NVARCHAR2(500),"" "CHANNEL_PRIVATEKEY" NCLOSEO靠我B, "MARK" NCLOB, "CREATE_TIME" DATE, "MODIFY_TIME""" DATE, "TS" DATE) PCTFREE 10 PCTUSED 40 INITRANSSEO靠我 1 MAXTRANS 255 STORAGE(I""NITIAL 131072 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFSEO靠我FE""R_POOL DEFAULT) TABLESPACE "HBS_DCORE_BASE2_DATA" LOGGING NOCOMPRESS LOB ("""CHANNEL_PRIVATEKEY"SEO靠我) STORE AS SECUREFILE (TABLESPACE "HBS_DCORE_BASE2_DATA""" ENABLE STORAGE IN ROW CHUNK 8192 RETENTIOSEO靠我N AUTO NOCACHE LOGGING NOCOMPRE""SS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1SEO靠我 BUFFER_""POOL DEFAULT)) LOB ("MARK") STORE AS SECUREFILE (TABLESPACE "HBS_DCORE_解决办法:在导出exp时先重命名导出数SEO靠我据的表空间,之后再重新导出dmp文件
**重命名表空间和还原表空间需要再dba权限下执行** 需要使用数据库服务器oracle用户,在$ORACLE_HOME/bin下执行,则进入dbaSEO靠我权限用户 ./sqlplus / as sysdba重命名表空间 alter tablespace HBS_DCORE_XXX_DATA rename to HBS_DSEO靠我ATA;导出数据(注意:该代码不需要使用sqlplus,直接登录数据库服务器oracle用户执行即可) exp HBS_DCORE_XXX/HBS_DCORE_XXX#2021@corSEO靠我e001 file=xxxnew.dmp log=xxx_exp.log statistics=none还原表空间名字 alter tablespace HBS_DATA renameSEO靠我 to HBS_DCORE_XXX_DATA ;之后使用导出的dmp导入新库即可当导入导出表空间一致时的其他报错
若imp导入出现下面报错则表明用户没有操作当前表空间的权限,需要进行授权报错样例1
IMPSEO靠我-00003: ORACLE error 1031 encountered ORA-01031: insufficient privileges IMP-00017: SEO靠我following statement failed with ORACLE error 1031: ""CREATE TRIGGER "HBS_DCORE_AML".T_AML_TESEO靠我L_CODE_TRI "" BEFORE INSERT ON T_AML_TEL_CODE "" FOR EACH ROW "" BEGIN SEO靠我 "" SELECT SEQ_T_AML_TEL_CODE.nextval INTO :NEW.ID FROM DUAL;" END;" IMP-00003: ORACLE errSEO靠我or 1031 encountered ORA-01031: insufficient privileges IMP-00017: following statemenSEO靠我t failed with ORACLE error 1031: ""CREATE TRIGGER "HBS_DCORE_AML".NBC_BLACKLIST_TRI SEO靠我"" BEFORE INSERT ON NBC_BLACKLIST "" FOR EACH ROW "" BEGIN "" SELECT SEQ_NBCSEO靠我_BLACKLIST.nextval INTO :NEW.ID FROM DUAL;" END;" IMP-00003: ORACLE error 1031 encountered SEO靠我 ORA-01031: insufficient privileges IMP-00017: following statement failed with ORACLE SEO靠我error 1031: ""CREATE TRIGGER "HBS_DCORE_AML".T_XXX_SMS_COLLECTION_TRI "" BEFORE INSESEO靠我RT ON T_XXX_SMS_COLLECTION "" FOR EACH ROW "" BEGIN "" SELECT SEQ_T_AML_SMS_SEO靠我COLLECTION.nextval INTO :NEW.ID FROM DUAL;" END;" IMP-00003: ORACLE error 1031 encountered SEO靠我 ORA-01031: insufficient privileges报错样例2
IMP-00017: 由于 ORACLE 错误 1950, 以下语句失败:"CREATE TABLE "SASEO靠我LGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER) P""CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANSSEO靠我 255 STORAGE(INITIAL 65536 FREELIST""S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) LOGGING NOCOM""PRESSSEO靠我" IMP-00003: 遇到 ORACLE 错误 1950 ORA-01950: 对表空间 EXAMPLE 无权限授权代码
授权需要再dba权限下执行 SEO靠我需要使用数据库服务器oracle用户,在$ORACLE_HOME/bin下执行,则进入dba权限用户 ./sqlplus / as sysdba 登录后 SEO靠我 grant connect ,resource to XXX_DCORE_XXX; alter user XXX_DCORE_XXX quota unlimited on XXX_DSEO靠我ATA;网站备案号:浙ICP备17034767号-2