oracle expdp impdp

ORACLE 导入导出数据

准备工作

1. 在备份目的路径建立备份文件夹

   # mk dump dir to save dump file
   cd ~
   mkdir oracle_dump
   

2. 用sys用户在oracle中创建逻辑目录

   SQL>create directory dump_path as ‘/home/oracle/oracle_dump‘;
   

3. 查看数据库中的逻辑目录 并授权

   SQL>select * from dba_directories;
   SQL>grant read,write on directory dump_dir to someone;
   

---

导出

1.按用户导出

---

# export
expdp user/pwd@orcl schemas=user(schemas) dumpfile=***.dmp directory=dump_dir;
# import 
# note if import to another user(schemas) or tablespace, REMAP_TABLESPACE, REMAP_SCHEMA needed, besides REMAP_DATAFILE, REMAP_TABLES maybe do.
impdp user/pwd@orcl schemas=user(schemas) dumpfile=***.dmp directory=dump_dir;

2.按表导出

---

# export
expdp user/pwd@orcl tables=a, b, c dumpfile=***.dmp directory=dump_dir;
# import 
# note if import to another user(schemas) or tablespace, REMAP_TABLESPACE, REMAP_SCHEMA needed, besides REMAP_DATAFILE, REMAP_TABLES maybe do.
impdp user/pwd@orcl tables=a, b, c dumpfile=***.dmp directory=dump_dir;

---

3.按条件导出

# export
expdp user/pwd@orcl query='where a = 10' dumpfile=***.dmp directory=dump_dir;
# import 
# note if import to another user(schemas) or tablespace, REMAP_TABLESPACE, REMAP_SCHEMA needed, besides REMAP_DATAFILE, REMAP_TABLES maybe do.
impdp user/pwd@orcl tables=a, b, c dumpfile=***.dmp directory=dump_dir;

---

4.按表空间导出

# export
expdp user/pwd@orcl tablespaces=a,b dumpfile=***.dmp directory=dump_dir;
# import 
# note if import to another user(schemas) or tablespace, REMAP_TABLESPACE, REMAP_SCHEMA needed, besides REMAP_DATAFILE, REMAP_TABLES maybe do.
impdp user/pwd@orcl tablespaces=a,b dumpfile=***.dmp directory=dump_dir;

---

5.导入导出整个数据库

# export
expdp user/pwd@orcl full=y dumpfile=***.dmp directory=dump_dir;
# import 
# note if import to another user(schemas) or tablespace, REMAP_TABLESPACE, REMAP_SCHEMA needed, besides REMAP_DATAFILE, REMAP_TABLES maybe do.
impdp user/pwd@orcl full=y dumpfile=***.dmp directory=dump_dir;

---

6. 向数据库追加记录

impdp user/pwd@orcl directory=dump_dir dumpfile=***.dmp schemas=systemtable_exists_action