数据泵导入导出操作

Myth丶恋晨 2021-09-21 01:54 376阅读 0赞

# 导入 #

第一步：  
查询DIRECTORY对象  
SELECT \* FROM dba\_directories;  
![在这里插入图片描述][20200109100443364.png]第二步：  
将dmp文件拷到指定位置  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5NTM5MA_size_16_color_FFFFFF_t_70]  
第三步：  
新建一个用户  
drop user puyun\_liangan cascade;  
create user puyun\_liangan  
identified by sa;  
– Grant/Revoke role privileges  
grant dba to puyun\_liangan;  
grant resource to puyun\_liangan ;  
grant connect to puyun\_liangan ;

第四步：  
执行导入语句

impdp puyun_liangan/sa@localhost:1521/orcl  DIRECTORY=DATA_PUMP_DIR DUMPFILE=PUYUN_LIANGAN_TEST.DMP remap_schema=puyun_liangan_test:puyun_liangan
    或
    impdp pbp/pbp@localhost:1521/orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=hg_20200208_pbp.DMP remap_schema=hg_20200208_pbp:pbp
    二选一,为模糊不清时有更多的对比

第五步：  
执行更新语句，修改密码为puyun360  
update org\_userinfo  
set spassword=‘c0433b643663cc83ccda9f0df07093f8’  
,email=’’  
,phone=’’;

第六步：  
事务提交  
修改D:\\puyun\\tomcat6\\conf\\Catalina\\localhostproject中name

# 导出 #

模板:expdp 用户名/密码@IP:端口号/Sid directory=DATA_PUMP_DIR dumpfile=文件路径.dmp
    以下任选其一
    
    
    
    --expdp puyun_cb_house/sa@localhost:1521/orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=puyun_cb_house_20190516.dmp logfile=puyun_cb_house_20190516.log  schemas=puyun_cb_house version=11.2.0.1.0 
    
    
    exp puyun_cb_house/sa@localhost:1521/orcl owner=puyun_cb_house file=D:\db\puyun_cb_house_expdb_20190510.dmp log=D:\db\exp.log grants=y  
    
    pause
    
    puyun_lens_erp_test
    
    exp puyun_lens_erp_test/sa@localhost:1521/orcl owner=puyun_lens_erp_test file=C:\Users\35854\Desktop\船舶\db\puyun_cb_house_expdb_20190510.dmp log=C:\Users\35854\Desktop\船舶\db\exp.log grants=y  
    
    pause
    
    
    exp puyun_cb_house/sa@localhost:1521/orcl owner=puyun_cb_house file=D:\db\puyun_cb_house_expdb_20190511.dmp log=D:\db\exp.log grants=y  
    
    pause
    
    
    imp puyun_zc_house/sa@localhost:1521/orcl file=d:/puyun_cb_house_expdb_20190511.dmp
    
    --impdp puyun_zc_house/sa@localhost:1521/orcl  DIRECTORY=DATA_PUMP_DIR DUMPFILE=FANG.DMP  remap_schema=fang:puyun_zc_house

[20200109100443364.png]: /images/20210920/52ec0375d3824f23a1bc33520683f82c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5NTM5MA_size_16_color_FFFFFF_t_70]: /images/20210920/ca389361fd3a4cbd9383bde61b4c109a.png