expdp/impdp导入导出基本操作(一)

expdp/impdp导入导出基本操作

1)建立目录，并赋给用户权限。ITPUB个人空间n&{ \Az

$h^,h,O%e;d$|~0[oracle@anpc ~]$ mkdir / Oracle/expdpITPUB个人空间e$t5Af,@

ITPUB个人空间L,L#P rJ v(Y0F)_

SQL>sqlplus / as sysdbaITPUB个人空间-v9vNmD5Q S5Y

O)Ks[ s0SQL> create or replace directory expdp_dir as '/Oracle/expdp';ITPUB个人空间1p5W#i)O F-n

%\j3}*P(Bv+L0Directory created.ITPUB个人空间
l(o;M Aj)yu5C)z

ITPUB个人空间QO*V]7]'b z

SQL> grant read,write on directory expdp_dir to scott;ITPUB个人空间5o"lX&eQ'Fo

ITPUB个人空间^^]6t(u@H_:B

Grant succeeded.

w m {*ju
{9W8k3y0

%b$GO&E+pC8j0SQL> select * from dba_directories where DIRECTORY_NAME='EXPDP_DIR';ITPUB个人空间c%QJ \V]s4]X v

ITPUB个人空间 L"q+]zCypSb z p

OWNER DIRECTORY_NAME DIRECTORY_PATH

CuFUWX0----- ------------------------- ----------------------------------------------------------------------ITPUB个人空间@.T'a_o1xd3j9Q8D

SYS EXPDP_DIR /Oracle/expdp

9R-D$X#t-vLD0

2)准备导入，导出

set ORACLE_SID=XXX ---for windows export ORACLE_SID=XXX -----FOR unix/linux

expdp system/system@oradb directory=expdp_dir dumpfile=expdp_fulldb_$f_name.dmp full=yITPUB个人空间0U}5a(s;j

impdp system/oracle full=y directory=impdp_dir dumpfile=FULL_01.DMP,FULL_02.DMP,FULL_03.DMP,FULL_04.DMP logfile=impdp.log

1、expdp/impdp导入导出模式：

}|&lO C{#t:w#G0

zHCzm{lS8C!T0(1)全库导出导入模式

5r {[ bw-sK0ITPUB个人空间
})T9w-D*T-z#f"i

expdp system/system@oradb directory=expdp_dir dumpfile=expdp_fulldb_$f_name.dmp full=yITPUB个人空间0U}5a(s;j

J'iL%[s%f"G J0impdp system/system@oradb directory=expdp_dir dumpfile=expdp_fulldb_$f_name.dmp full=yITPUB个人空间DoUkF# dvN

&x(F5bv4f6e0(2)用户导出模式

,oh \6Z,h T7s
{0ITPUB个人空间u%R+x6Ae+Ka

expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp SCHEMAS=scott

1Aos%L Y,Dn&r)~0

-E
ga/V `#f0impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_01_$f_name.dmp,scott_expdp_02_$f_name.dmp remap_schema=scott:scottITPUB个人空间aQ+v&l#n^#T

h1N#k&W(})w4R&D0(3)表导出模式

)x FU*pzh0

~in |;{g p0expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp tables=scott.emp, scott.dept

9j-a+X"q_,wz0注：使用query条件的语句时最好采用parfile方式可以按条件导出数据。ITPUB个人空间SN,WT Y(j O

Q%VJ9j3gO$H0impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp tables=scott.emp, scott.dept

LG3f1agD0a0

~ K'@^Q Kvk0impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp schemas=scott table_exists_action=append

|y n"p,G8\0注：追加数据。

;ri)HS:_D"P0t0ITPUB个人空间}t;j+Y.Z"XX

(4)表空间导出模式ITPUB个人空间3 h m#A2V+Xg8R

ITPUB个人空间g`cj

expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%U_$f_name.dmp tablespaces=tbs1,tbs2ITPUB个人空间 c6E4 ct}-b%S

AI+i!F5G K.y0impdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_01_$f_name.dmp tablespaces=tbs1,tbs2ITPUB个人空间|'BBv+N
EOLp

6q _%@C0Q B0(5)可移动表空间导出模式ITPUB个人空间E8|0u%hb\|v2w#Q

t4U!O5d4wj0expdp system/system@oradb directory=expdp_dir dumpfile=scott_expdp_%