数据库导入导出脚本

最新推荐文章于 2024-07-14 03:55:24 发布

DEMO_LOMO

最新推荐文章于 2024-07-14 03:55:24 发布

阅读量1.2k

点赞数

本文链接：https://blog.csdn.net/DEMO_LOMO/article/details/52035979

版权

数据泵导入（导入到指定表空间）
impdp czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=CZCK201511.DMP REMAP_SCHEMA=fxgl:fxgl REMAP_TABLESPACE='(zhzs_portal:czck,fxgl:czck)'
<< REMAP_TABLESPACE='(USERS1：FXGL,user2:fxgl)' >>

impdp a6/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=a60115.DMP REMAP_SCHEMA=a6:a6 REMAP_TABLESPACE=a6:czck transform=oid：n

impdp czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=czck0115.DMP REMAP_SCHEMA=czck:czck REMAP_TABLESPACE=czck:czck

impdp jzb_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_FXGL0225.DMP REMAP_SCHEMA=ts_fxgl:jzb_fxgl REMAP_TABLESPACE=ts_czck:jzb_czck,ts_fxgl:jzb_czck （基准版）

impdp jzb_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_CZCK0225.DMP REMAP_SCHEMA=ts_czck:jzb_czck REMAP_TABLESPACE=ts_czck:jzb_czck,ts_fxgl:jzb_czck （基准版）

impdp jz_gs/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_2015-08-26.DMP REMAP_SCHEMA=a6:jz_gs,a6_jz:jz_gs,fxgl:jz_gs REMAP_TABLESPACE=USERS:JZ_GS,FXGL:JZ_GS（186焦作国税）

impdp jz_ds/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_20150826.DMP REMAP_SCHEMA=fxgl:jz_ds REMAP_TABLESPACE=USERS:JZ_DS,FXGL:JZ_DS（186焦作地税）

impdp jz_a6/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP REMAP_SCHEMA=a6:jz_a6,a6_jz:jz_a6 REMAP_TABLESPACE=USERS:JZ_CZCK,CZCK:JZ_CZCK,FXGL:JZ_CZCK（186焦作财政）

impdp jz_cz/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=czck0206.DMP REMAP_SCHEMA=czck:jz_cz,czck_jz:jz_czck REMAP_TABLESPACE=CZCK:JZ_CZCK,FXGL:JZ_CZCK,DSCK:JZ_CZCK（186焦作财政）

impdp jz_cspt/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=CSPT1029.DMP REMAP_SCHEMA=cspt:jz_cspt REMAP_TABLESPACE=USERS:JZ_CZCK,CZCK:JZ_CZCK,FXGL:JZ_CZCK,DSCK:JZ_CZCK,SP:JZ_CZCK（焦作财政）

impdp fxgl_product/1@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_FXGL0226.DMP REMAP_SCHEMA=fxgl_product:fxgl_product REMAP_TABLESPACE=ts_czck:jzb_czck,ts_fxgl:jzb_czck

impdp ts_fxgl_16/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_20160227.DMP REMAP_SCHEMA=fxgl:ts_fxgl_16,czck:ts_fxgl_16 REMAP_TABLESPACE=czck:ts_fxgl_16,fxgl:ts_fxgl_16（16年唐山）

impdp ts_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_CZCK0225.DMP REMAP_TABLESPACE=ts_czck:jz_gs,ts_fxgl:jz_gs transform=oid:n（原基准版）

impdp ts_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=TS_FXGL0225.DMP REMAP_TABLESPACE=ts_czck:czck,ts_fxgl:czck transform=oid:n（原基准版）

impdp da_a6/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=a60115.DMP REMAP_SCHEMA=a6:da_a6 REMAP_TABLESPACE=a6:jz_gs,czck:jz_gs transform=oid:n（德安环境）

impdp da_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=czck0115.DMP REMAP_SCHEMA=czck:da_czck REMAP_TABLESPACE=czck:jz_gs transform=oid:n（德安环境）

IMPDP cy_a6/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE=a6_20160305.dmp REMAP_SCHEMA=a6:cy_a6 transform=oid:n （51朝阳环境3.10）
IMPDP cy_czck/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE= czck_20160305.dmp REMAP_SCHEMA=czck:cy_czck REMAP_TABLESPACE=dsck:czck,TBS_YY_IDX:czck transform=oid:n（51朝阳环境3.10）
IMPDP a6_cy/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE= a6_cy_20160305.dmp transform=oid:n（51朝阳环境3.10）
IMPDP db_dw/oracle@orcl directory=DATA_PUMP_DIR DUMPFILE= db_dw_20160308.dmp transform=oid:n（51朝阳环境3.10）

impdp eeds_fxgl02/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL_20160311.DMP REMAP_SCHEMA=eeds_fxgl01:eeds_fxgl02 REMAP_TABLESPACE=EEDS_FXGL01:eeds_fxgl02 transform=oid:n

impdp bq82/1@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=BQ82_20160316.DMP REMAP_SCHEMA=bq82_erds:bq82,bq82_eeds:bq82 transform=oid:n

impdp jz_a6/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP transform=oid:n（186焦作财政）(表空间jz_czck_a6)
impdp jz_cz/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP transform=oid:n(jz_czck)
impdp jz_cspt/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=A6_JZ1218.DMP transform=oid:n(jz_cspt)

impdp jj_a6/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=A60309.DMP REMAP_SCHEMA=a6:jj_a6 REMAP_TABLESPACE=a6:jj_a6,czck:jj_a6 transform=oid:n
impdp jj_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=CZCK0309.DMP REMAP_SCHEMA=czck:jj_czck REMAP_TABLESPACE=a6:jj_czck,czck:jj_czck transform=oid:n

impdp dan_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=jzb_fxgl20160321.DMP REMAP_SCHEMA=jzb_fxgl:dan_fxgl REMAP_TABLESPACE=jzb_czck:dan （德安新版）

impdp dan_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=jzb_czck20160321.DMP REMAP_SCHEMA=jzb_czck:dan_czck REMAP_TABLESPACE=jzb_czck:dan （德安新版）

impdp jzb_czck/oracle@orcl1 DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK(20160328).DMP REMAP_SCHEMA=jzb_czck:jzb_czck REMAP_TABLESPACE=jzb_czck:jzb_czck,jz_gs:jzb_czck transform=oid:n

impdp jzb_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK(20160405).DMP REMAP_TABLESPACE=jzb_czck:czck transform=oid:n(九江县)
impdp jzb_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_FXGL20160323.DMP REMAP_TABLESPACE=jzb_czck:czck transform=oid:n(九江县)

impdp fxgl/oracle@gqczhzs DIRECTORY=DATA_PUMP_DIR dumpfile=FXGL20160407.DMP transform=oid:n(共青)

impdp jzb_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_FXGL20160323.DMP REMAP_SCHEMA=a6:jzb_fxgl,fxgl:jzb_fxgl REMAP_TABLESPACE=jzb_fxgl:czck,jzb_czck:czck

impdp jzb_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK(20160405).DMP REMAP_TABLESPACE=jzb_czck:czck
impdp jzb_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR dumpfile=JZB_CZCK0328.DMP REMAP_SCHEMA=jzb_czck:jzb_czck REMAP_TABLESPACE=jzb_czck:czck version=11.2.0.1.0

数据导入：
imp fxgl/oracle@orcl file=D:\app\zhudong\admin\orcl\dpdump\fxgl.DMP full=y log=D:\app\zhudong\admin\orcl\dpdump\import.log

imp jt_a6/oracle@orcl file=G:\gongzuorenwuwushan\九台\oracle数据备份\oracle数据备份\a6.dmp fromuser=a6 touser=jt_a6 （51九台环境3.10）
imp jt_czck/oracle@orcl file=G:\gongzuorenwuwushan\九台\oracle数据备份\oracle数据备份\czck.dmp fromuser=czck touser=jt_czck（51九台环境3.10）
imp jt_sjcj/oracle@orcl file=G:\gongzuorenwuwushan\九台\oracle数据备份\oracle数据备份\sjcj.dmp fromuser=sjcj touser=jt_sjcj（51九台环境3.10）

imp bq82/1@orcl file=D:bq820160316.DMP full=y

select * from sys_usermanage where user_id='sa' for update

数据泵导出
Expdp system/manager@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=full.dmp FULL=Y ' logfile=日志名字.log

Expdp fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=full.dmp FULL=Y ' logfile=日志名字.log
(去掉full=y是备份指定用户)

Expdp ts_fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=ts_fxgl0225.dmp
Expdp ts_czck/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=ts_czck0225.dmp

Expdp jz_cz/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=jz_cz0316.dmp
Expdp jz_cspt/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=jz_cspt0316.dmp
Expdp jz_a6/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=jz_a60316.dmp

Expdp fxgl/oracle@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=fxgl20160407.dmp(共青)

1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表table1 、table2导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1,table2)

exp fxgl/oracle@gqczhzs file=d:\table.dmp table=uq_blob
imp a6/oracle@gqczhzs file=d:\table.dmp fromuser=fxgl touser=a6