oracle中expdp,oracle的expdp和impdp

expdp impdp

1、创建目录

create directory expdp as '/u02/';

2、授权用户访问此目录

grant read,write on directory expdp to scott;

expdp scott/tiger dumpfile=scott.emp directory=expdp

schemas=SCOTT;

expdp scott/tiger dumpfile=scott.bak directory=expdp

tables=emp,dept;

expdp scott/tiger dumpfile=expdp:scott.emp

tables=emp,dept;

expdp \'sys/oracle as sysdba\' full=y

dumpfile=exp:full.bak

--direatory谁建立谁本身就有权限，怒需要单独分配

drop table scott.emp purge;

drop table scott.dept purge;

impdp scott/tiger dumpfile=expdp:scott.bak

tables=emp,dept;

select * from tab;

--append追加

delete emp;

impdp scott/tiger dumpfile=expdp:scott.bak tables=emp;

--skip跳过表，不会导入数据

impdp scott/tiger dumpfile=expdp:scott.bak tables=emp

table_exists_action=append；

--replace替换数据不释放空间

update emp set sal=sal+100;

cmomit;

impdp scott/tiger dumpfile=expdp:scott.bak tables=emp

table_exists_action=replace;

--truncate释放空间后插入数据

update emp set sal=sal+1;

cmomit;

impdp scott/tiger dumpfile=expdp:scott.bak tables=emp

table_exists_action=truncate;

--skip 无则加入，有则跳过

drop table scott.salgrade purge;

impdp scott/tiger dumpfile=expdp:scott.emp

tables=emp,dept.salgrade table_exists_action=skip;

--参数文件模式

userid=scott/tiger

dumpfile=expdp:scott.dmp

table_exists_action=truncate

impdp parfile=/u02/dp.txt

--remap_schema从A用户到B用户

impdp system/oracle dumpfile=expdp:scott.bak

remap_schema=scott:qin;

eg:

expdp system/oracle directory=dump_qin dumpfile=qin_%U.dmp

logfile=expdp_qin.log schemas=(wang,qin)

impdp system/oracle directory=dump_qin

dumpfile=qin_%U.dmp logfile=impdp_qin.log

schemas=(wang,qin)