{"id":1215,"date":"2016-01-03T10:56:43","date_gmt":"2016-01-03T01:56:43","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1215"},"modified":"2018-09-11T14:33:42","modified_gmt":"2018-09-11T05:33:42","slug":"datapump-expdp-impdp","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1215","title":{"rendered":"Datapump – expdp, impdp"},"content":{"rendered":"

 Oracle datapump\ub294 oracle 10g \ubc84\uc804\ubd80\ud130 \ub4f1\uc7a5\ud55c export\/import \uc758 \ud5a5\uc0c1\ub41c \uc720\ud2f8\ub9ac\ud2f0\uc785\ub2c8\ub2e4.<\/span><\/p>\n


<\/span><\/div>\n
1. Datapump\uc758 \uc7a5\uc810<\/strong><\/span><\/div>\n
-\uc791\uc5c5 \uad00\ub9ac\uc758 \ud3b8\uc774\uc131 : \uc791\uc5c5 \uc911\uc9c0\uac00 \uac00\ub2a5\ud568.(job\uc758 \uc81c\uc5b4\uac00 \uac00\ub2a5)<\/span><\/div>\n
-\ud544\uc694\ud55c \ub514\uc2a4\ud06c \uacf5\uac04\uc758 \uc608\uce21 : ESTIMATE \ud30c\ub77c\ubbf8\ud130\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud574\ub2f9 \uc791\uc5c5 \uc2dc \ud544\uc694\ud55c \ub514\uc2a4\ud06c \uacf5\uac04 \uc608\uce21<\/span><\/div>\n
-\uc6d0\uaca9\uc9c0 DB\uc5d0 \uc791\uc5c5 \uc218\ud589 \uac00\ub2a5 : DBLINK \uae30\ub2a5\uc744 \ud1b5\ud574 \uc6d0\uaca9\uc9c0\uc5d0 \uc788\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0 expdp\/impdp \uc218\ud589\uac00\ub2a5<\/span><\/div>\n
-remapping \uae30\ub2a5 \uc9c0\uc6d0 : \uc2a4\ud0a4\ub9c8 \ubcc0\uacbd\uc774\ub098 \ud14c\uc774\ube14 \uc2a4\ud398\uc774\uc2a4 \ubcc0\uacbd, \ub370\uc774\ud130 \ud30c\uc77c \ubcc0\uacbd\uae4c\uc9c0 \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/span><\/div>\n
-dump \uc791\uc5c5\ud558\uba74\uc11c \uc555\ucd95\uc744 \ub3d9\uc2dc\uc5d0 \uc9c4\ud589 : \uc6a9\ub7c9\uc774 \ud070 \ub370\uc774\ud130\uc758 \uacbd\uc6b0 \uc555\ucd95\uc744 \ub3d9\uc2dc\uc5d0 \uc9c4\ud589\ud558\ubbc0\ub85c dump file \uc6a9\ub7c9\uc744 \ud68d\uae30\uc801\uc73c\ub85c \uc904\uc77c \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/span><\/div>\n
-\uc544\uc8fc \ube68\ub77c\uc9c4 \uc18d\ub3c4 : \uc11c\ubc84\uc758 \ud658\uacbd\uc5d0 \ub530\ub77c \ub2e4\ub974\uc9c0\ub9cc \uc77c\ubc18\uc801\uc73c\ub85c \uc774\uc804 \uc800\ubc88\uc758 exp\/imp\uc640 \ube44\uad50\ud574 \ud3c9\uade0\uc801\uc73c\ub85c 20\ubc30 \uc774\uc0c1\uc758 \uc131\ub2a5\ud5a5\uc0c1<\/span><\/div>\n

<\/span><\/div>\n
2. \uc0ac\uc6a9 \uc804 \ud658\uacbd \uc124\uc815\ud558\uae30<\/strong><\/span><\/div>\n
datapump\ub294 exp\/imp\uc640 \ub2e4\ub974\uac8c \uc720\ud2f8\ub9ac\ud2f0\uac00 \uc9c1\uc811 OS \ud30c\uc77c\uc5d0 I\/O\ub97c \ud560 \uc218 \uc5c6\uace0 \uc624\ub77c\ud074\uc5d0 directory\ub77c\ub294 \uac1d\uccb4\ub97c \ud1b5\ud574\uc11c \uac04\uc811\uc73c\ub85c \uc811\uadfc \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/span><\/div>\n
\uadf8\ub798\uc11c datapump\ub97c \uc0ac\uc6a9\ud558\ub824\uba74 \ubbf8\ub9ac directory\uac00 \ub9cc\ub4e4\uc5b4\uc838 \uc788\uc5b4\uc57c \ud558\uba70 datapump\ub97c \uc218\ud589\ud558\ub294 \uc0ac\uc6a9\uc790\ub294 \uadf8 directory\uc5d0 \uc811\uadfc\ud560 \uc218 \uc788\ub294 \uad8c\ud55c\uc774 \uc788\uc5b4\uc57c\ud569\ub2c8\ub2e4<\/span><\/div>\n
\uc774 \uae30\ub2a5\uc744 \ud1b5\ud574 DBA\ub294 datapump\uc758 \ubcf4\uc548\uad00\ub9ac\uae4c\uc9c0 \uac00\ub2a5\ud558\uac8c \ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/span><\/div>\n

<\/span><\/div>\n
$mkdir \/data\/datapump<\/span><\/div>\n

<\/span><\/div>\n
SQL>create or replace directory datapump as ‘\/data\/datapump’; <–directory \uc0dd\uc131<\/span><\/div>\n
SQL>grant read,write on directory datapump to scott; <–\ud574\ub2f9 \ub514\ub809\ud1a0\ub9ac\uc5d0 read,write \uad8c\ud55c \ubd80\uc5ec<\/span><\/div>\n
SQL>grant create any directory to scott; <–scott\uc5d0\uac8c directroy \uad8c\ud55c \ubd80\uc5ec<\/span><\/div>\n

<\/span><\/div>\n
3. expdp \uc2e4\ud589 \ubaa8\ub4dc<\/strong><\/span><\/div>\n
-full mode : full \ud30c\ub77c\ubbf8\ud130 \uc0ac\uc6a9\ud558\uc5ec database \uc804\uccb4 export<\/span><\/div>\n
-schema mode : schemas \ud30c\ub77c\ubbf8\ud130 \uc0ac\uc6a9\ud558\uc5ec \uc2a4\ud0a4\ub9c8 \uc804\uccb4 export<\/span><\/div>\n
-tablespace mode : tablespaces \ud30c\ub77c\ubbf8\ud130 \uc0ac\uc6a9\ud558\uc5ec tablespace \uc804\uccb4 export<\/span><\/div>\n
-table mode : tables \ud30c\ub77c\ubbf8\ud130\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud14c\uc774\ube14\uc744 export<\/span><\/div>\n

<\/span><\/div>\n
#1 scott \uacc4\uc815\uc758 emp,dept \ud14c\uc774\ube14\ub9cc \ubc31\uc5c5\ubc1b\uae30<\/span><\/div>\n
$expdp scott\/tiger tables=emp,dept directory=datapump job_name=t1 dumpfile=emp_dept.dmp<\/span><\/div>\n
-dumpfile\uc740 \ud30c\uc77c\uba85 \ub9cc \uba85\uc2dc\ud574\uc90c.<\/span><\/div>\n

<\/span><\/div>\n
#2 scott schema \uc804\ubd80 \ubc31\uc5c5 \ubc1b\uae30<\/span><\/div>\n
$expdp scott\/tiger schemas=scott directory=datapump dumpfile=scott01.dmp<\/span><\/div>\n

<\/span><\/div>\n
#3 DB \uc804\uccb4 \ubc31\uc5c5 \ubc1b\uae30<\/span><\/div>\n
$expdp system\/oracle full=y directory=datapump dumpfile=full01.dmp job_name=a<\/span><\/div>\n

<\/span><\/div>\n
#4 \uc77c\uc2dc \uc911\ub2e8 \ud6c4 \ub2e4\uc2dc \uc791\uc5c5\ud558\uae30***<\/span><\/div>\n
$expdp systme\/oracle full=y directory=datapump dumpfile=full02.dmp job_name=a <–job_name \ud544\uc218 \uc9c0\uc815<\/span><\/div>\n

<\/span><\/div>\n
CTRL+C \ub20c\ub7ec \ucde8\uc18c<\/span><\/div>\n
Export>status <–\uc0c1\ud0dc\ud655\uc778<\/span><\/div>\n
Export>stop job <–\ud604\uc7ac job \uc911\uc9c0<\/span><\/div>\n

<\/span><\/div>\n
col owner_name for a10<\/span><\/div>\n
col job_name for a10<\/span><\/div>\n
col operation for a10<\/span><\/div>\n
col job_mode for a10<\/span><\/div>\n
select owner_name, job_name,operation,job_mode,state from dba_datapump_jobs;   <–datapump job\uc758 \uc0c1\ud0dc\ud655\uc778<\/span><\/div>\n

<\/span><\/div>\n
$expdp system\/oracle attache=system.a <–\uc911\ub2e8\ub41c \uc791\uc5c5 \uc7ac\uc2dc\uc791<\/span><\/div>\n
Export>start job<\/span><\/div>\n
Export>status<\/span><\/div>\n

<\/span><\/div>\n
#5 \ube44\uc815\uc0c1\uc801\uc73c\ub85c \uc885\ub8cc\ub41c job \ucde8\uc18c\ud558\uae30<\/span><\/div>\n
set line 200<\/span><\/div>\n
col owner.object for a15<\/span><\/div>\n
select o.status, o.object_id, o.object_type, o.owner||’.’||object_name “OWNER.OBJECT” from dba_object o, dba_datapump_jobs j where o.owner=j.owner_name and o.object_name=j.job_name and j.job_name not like ‘BIN$%’ order by 4,2;<\/span><\/div>\n

<\/span><\/div>\n
SQL>drop table system.dp1;<\/span><\/div>\n
SQL>drop table system.dp2;<\/span><\/div>\n
SQL>drop table system.dp3;<\/span><\/div>\n

<\/span><\/div>\n
select owner_name, job_name, operation,job_mode, state from dba_datapump_jobs; <– no rows, \ubaa8\ub450 \uc0ad\uc81c\ub410\ub2e4.<\/span><\/div>\n

<\/span><\/div>\n
#6 \uc5ec\ub7ec \uc0ac\uc6a9\uc790\uc758 \ud14c\uc774\ube14 \ud55c\uaebc\ubc88\uc5d0 expdp \ubc1b\uae30<\/span><\/div>\n
$expdp system\/oracle directory=datapump dumpfile=scott16.dmp tables=scott.emp, hr.departments<\/span><\/div>\n

<\/span><\/div>\n
#7 \ubcd1\ub82c\ub85c expdp \uc791\uc5c5\ud558\uae30<\/span><\/div>\n
$expdp system\/oracle full=y directory=datapump dumpfile=full04.dmp job_name=a parallel=4 <–top -c\ub85c \ud655\uc778<\/span><\/div>\n

<\/span><\/div>\n
#8 <\/span><\/div>\n
$expdp system\/oracle full=y parallel=4 dumpfile=datadir1:full1%U.dat, datadir2:full2%U.dat, datadir3%U.dat, datadir4%U.dat filesize=100m<\/span><\/div>\n
datadir1~4 mkdir\ubc0f driectory \ucd94\uac00 \ubc0f grant \ub418\uc5b4\uc788\uc5b4\uc57c\ud568<\/span><\/div>\n

<\/span><\/div>\n
#9 \ud30c\ub77c\ubbf8\ud130 \ud30c\uc77c \uc0ac\uc6a9\ud574\uc11c expdp \uc218\ud589 – \uc5ec\ub7ec \uac1c\uc758 \ud30c\uc77c\ub85c \ubd84\ud560 expdp<\/span><\/div>\n
$vi expdp_pump.par<\/span><\/div>\n
userid=system\/oracle<\/span><\/div>\n
directory=datapump<\/span><\/div>\n
job_name=datapump<\/span><\/div>\n
logfile=expdp.log<\/span><\/div>\n
dumpfile=expdp_%U.dmp<\/span><\/div>\n
filesize=100M<\/span><\/div>\n
full=y<\/span><\/div>\n
:wq!<\/span><\/div>\n

<\/span><\/div>\n
$expdp parfile=expdp_pump.par<\/span><\/div>\n

<\/span><\/div>\n
4. impdp \uc0ac\uc6a9\ud558\uae30<\/strong><\/span><\/div>\n

<\/span><\/div>\n
#1 parameter \ud30c\uc77c \uc774\uc6a9\ud574\uc11c impdp \uc791\uc5c5\ud558\uae30 <\/span><\/div>\n
$vi impdp.par<\/span><\/div>\n
userid=system\/oracle<\/span><\/div>\n
directory=datapump<\/span><\/div>\n
job_name=datapump<\/span><\/div>\n
logfile=impdp_pump.log<\/span><\/div>\n
dumpfile=expdp_%U.dmp<\/span><\/div>\n
full=y<\/span><\/div>\n
table_exists_action=append<\/span><\/div>\n
:wq!<\/span><\/div>\n
$impdp parfile=impdp.par<\/span><\/div>\n

<\/span><\/div>\n
#2 impdp \ubcd1\ub82c \uc791\uc5c5\ud558\uae30<\/span><\/div>\n
$impdp system\/oracle parallel=4 dumpfile=datadir1:full1%U.dat, datadir2:full2%U.dat, datadir3%U.dat, datadir4%U.dat table_exists_action=append<\/span><\/div>\n

<\/span><\/div>\n
#3 import \uc218\ud589\ud558\uc9c0 \uc54a\uace0 DDL \ubb38\uc7a5\ub9cc \ucd94\ucd9c\ud558\uae30<\/span><\/div>\n
$impdp system\/oracle directory=datapump dumpfile=expdp_%U.dmp sqlfile=datapump.dat<\/span><\/div>\n

<\/span><\/div>\n
#4 \uc791\uc5c5 \uc608\uc0c1\uc2dc\uac04 \ucd94\ucd9c\ud558\uae30<\/span><\/div>\n
select sid,serial#,sofar,totalwork from v$session_longops where opname=’DATAPUMP’ and sofar !=totalwork;<\/span><\/div>\n
                                                                                                               job_name \ub300\ubb38\uc790 \uc785\ub825<\/span><\/div>\n

<\/span><\/div>\n
#5 \ub370\uc774\ud130 \ud38c\ud504 \uc7ac \uc124\uce58\ud558\uae30 (10.2 \uc774\uc0c1 \ubc84\uc804)<\/span><\/div>\n

SQL>@$ORACLE_HOME\/rdbms\/admin\/catdph.sql
SQL>@$ORACLE_HOME\/rdbms\/admin\/prvtdtde.plb
SQL>@$ORACLE_HOME\/rdbms\/admin\/catdpb.sql
SQL>@$ORACLE_HOME\/rdbms\/admin\/dbmspump
SQL>@$ORACLE_HOME\/rdbms\/admin\/utlrp.sql<\/p>\n


<\/span><\/div>\n

<\/span><\/div>\n
#6 \uc124\uc815\ub41c directory \uacbd\ub85c \ud655\uc778\ud558\uae30<\/span><\/div>\n
set line 200<\/span><\/div>\n
col owner for a10<\/span><\/div>\n
col directory_name for a25<\/span><\/div>\n
col directory_path for a60<\/span><\/div>\n
select * from dba_directories;<\/span><\/div>\n

<\/span><\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

 Oracle datapump\ub294 oracle 10g \ubc84\uc804\ubd80\ud130 \ub4f1\uc7a5\ud55c export\/import \uc758 \ud5a5\uc0c1\ub41c \uc720\ud2f8\ub9ac\ud2f0\uc785\ub2c8\ub2e4. 1. Datapump\uc758 \uc7a5\uc810 -\uc791\uc5c5 \uad00\ub9ac\uc758 \ud3b8\uc774\uc131 : \uc791\uc5c5 \uc911\uc9c0\uac00 \uac00\ub2a5\ud568.(job\uc758 \uc81c\uc5b4\uac00 \uac00\ub2a5) -\ud544\uc694\ud55c \ub514\uc2a4\ud06c \uacf5\uac04\uc758 \uc608\uce21 : ESTIMATE \ud30c\ub77c\ubbf8\ud130\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud574\ub2f9 \uc791\uc5c5 \uc2dc \ud544\uc694\ud55c \ub514\uc2a4\ud06c \uacf5\uac04 \uc608\uce21 -\uc6d0\uaca9\uc9c0 DB\uc5d0 \uc791\uc5c5 \uc218\ud589 \uac00\ub2a5 : DBLINK \uae30\ub2a5\uc744 \ud1b5\ud574 \uc6d0\uaca9\uc9c0\uc5d0 \uc788\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0 expdp\/impdp \uc218\ud589\uac00\ub2a5 -remapping \uae30\ub2a5 \uc9c0\uc6d0 : […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[1127,284,1128,1129],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1215"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1215"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1215\/revisions"}],"predecessor-version":[{"id":4154,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1215\/revisions\/4154"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1215"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1215"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1215"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}