ORACLE数据泵导入导出表结构和数据
- 用Oracle用户远程登录源数据库
- 切换到SQL命令行:sqlplus / as sysdba
- 查询dba目录:SQL>select * from dba_directories;
- 找到dba目录列表,选定/home/oracle/data_bak目录,查看该目录对应的DIRECTORY_NAME
- 导出指定表:expdp db/123456@10.128.205.**:1521/orcl directory=DIR dumpfile=table_data_20200922.dmp tables=t_pf_project_info, t_pf_fund_info, t_pf_bankacco_info, t_pf_record_voucher_info, t_pf_voucher_entry_info, t_od_bank_receipt_info, t_od_originaldocument_file, t_od_chargedetails_info, t_od_chargeworkflow_info, t_pf_bankserialno_info, t_pf_bankbalance_info,t_sys_data_collection_config;
- 用Oracle用户远程登录目标数据库SQL>select * from dba_directories;
- 查询dba目录:SQL>select * from dba_directories;3. 找到dba目录列表,选定目录/home/oracleData,查看该目录对应的DIRECTORY_NAME
- 退出sqlplus:SQL>exit
- 将第4步目录下新建的dmp文件拷贝至第8步的目录下
- 导入:impdp db/123456@10.128.202.**:1521/orcl table_exists_action= replace directory=UAT_DB dumpfile=table_data_20200922.dmp REMAP_SCHEMA=db:db remap_tablespace=IFA_DB:IFA_DB;
注:注意table_exists_action的类型,REMAP_SCHEMA = 源数据库用户名/目标数据库用户名,remap_tablespace=源数据库表空间/目标数据库表空间
链接: link.