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)