Oracle数据库expdp与impdp

一、Oracle数据泵

expdp -help

impdp -help

1.创建目录

sql

create directory [directory_name] as [os path];
create directory backup as 'c:\oracle\backup'; --windows
create directory backup as '/oracle/backup'; --linux

2.查询目录

sql

select * from dba_directories

3.目录访问用户授权

sql

grant read,write on directory backup to dbuser;

4.导入导出用户授权

sql

grant exp_full_database,imp_full_database to dbuser;

5.使用expdp命令导出实例

(1)导出dbuser用户下的所有对象

shell

expdp dbuser/password@orcl dumpfile=expdp.dmp directory=backup logfile=expdp.log schemas=dbuser

(2)数据泵压缩备份(多文件备份)

shell

expdp dbuser/password@orcl dumpfile=expdp_%U.dmp directory=backup logfile=expdp.log schemas=dbuser parallel=5 compression=all

(3)导出dbuser用户下的指定表

shell

expdp dbuser/password@orcl dumpfile=expdp.dmp directory=backup logfile=expdp.log tables=table_name1,table_name2 
expdp dbuser/password@orcl dumpfile=expdp.dmp directory=backup logfile=expdp.log tables=('table_name1','table_name2')

二、impdp数据导入

1.登录Oracle数据库

highlighter- actionscript

sqlplus / as sysdba

2.创建目录

sql

create directory 目录名称(可自定义命名) as ' 数据库导出文件存放路径';

create directory backup as 'c:\oracle\backup'; --windows

create directory backup as '/oracle/backup'; --linux

3.查询目录

sql

select * from dba_directories

4.目录访问用户授权

sql

grant read,write on directory data_dir to dbuser;

5.导入导出用户授权

sql

grant exp_full_database,imp_full_database to dbuser;

6.使用cmd命令导出

highlighter- routeros

impdp impuser/password@orcl REMAP_SCHEMA=expuser:impuser table_exists_action=replace directory=data_dir dumpfile=impdp.dmp logfile=expdp.log