Datapump – expdp, impdp

 Oracle datapump는 oracle 10g 버전부터 등장한 export/import 의 향상된 유틸리티입니다.

1. Datapump의 장점
-작업 관리의 편이성 : 작업 중지가 가능함.(job의 제어가 가능)
-필요한 디스크 공간의 예측 : ESTIMATE 파라미터를 사용하여 해당 작업 시 필요한 디스크 공간 예측
-원격지 DB에 작업 수행 가능 : DBLINK 기능을 통해 원격지에 있는 데이터베이스에 expdp/impdp 수행가능
-remapping 기능 지원 : 스키마 변경이나 테이블 스페이스 변경, 데이터 파일 변경까지 가능합니다.
-dump 작업하면서 압축을 동시에 진행 : 용량이 큰 데이터의 경우 압축을 동시에 진행하므로 dump file 용량을 획기적으로 줄일 수 있습니다.
-아주 빨라진 속도 : 서버의 환경에 따라 다르지만 일반적으로 이전 저번의 exp/imp와 비교해 평균적으로 20배 이상의 성능향상

2. 사용 전 환경 설정하기
datapump는 exp/imp와 다르게 유틸리티가 직접 OS 파일에 I/O를 할 수 없고 오라클에 directory라는 객체를 통해서 간접으로 접근 가능합니다.
그래서 datapump를 사용하려면 미리 directory가 만들어져 있어야 하며 datapump를 수행하는 사용자는 그 directory에 접근할 수 있는 권한이 있어야합니다
이 기능을 통해 DBA는 datapump의 보안관리까지 가능하게 되었습니다.

$mkdir /data/datapump

SQL>create or replace directory datapump as ‘/data/datapump’; <–directory 생성
SQL>grant read,write on directory datapump to scott; <–해당 디렉토리에 read,write 권한 부여
SQL>grant create any directory to scott; <–scott에게 directroy 권한 부여

3. expdp 실행 모드
-full mode : full 파라미터 사용하여 database 전체 export
-schema mode : schemas 파라미터 사용하여 스키마 전체 export
-tablespace mode : tablespaces 파라미터 사용하여 tablespace 전체 export
-table mode : tables 파라미터를 사용하여 테이블을 export

#1 scott 계정의 emp,dept 테이블만 백업받기
$expdp scott/tiger tables=emp,dept directory=datapump job_name=t1 dumpfile=emp_dept.dmp
-dumpfile은 파일명 만 명시해줌.

#2 scott schema 전부 백업 받기
$expdp scott/tiger schemas=scott directory=datapump dumpfile=scott01.dmp

#3 DB 전체 백업 받기
$expdp system/oracle full=y directory=datapump dumpfile=full01.dmp job_name=a

#4 일시 중단 후 다시 작업하기***
$expdp systme/oracle full=y directory=datapump dumpfile=full02.dmp job_name=a <–job_name 필수 지정

CTRL+C 눌러 취소
Export>status <–상태확인
Export>stop job <–현재 job 중지

col owner_name for a10
col job_name for a10
col operation for a10
col job_mode for a10
select owner_name, job_name,operation,job_mode,state from dba_datapump_jobs;   <–datapump job의 상태확인

$expdp system/oracle attache=system.a <–중단된 작업 재시작
Export>start job

#5 비정상적으로 종료된 job 취소하기
set line 200
col owner.object for a15
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;

SQL>drop table system.dp1;
SQL>drop table system.dp2;
SQL>drop table system.dp3;

select owner_name, job_name, operation,job_mode, state from dba_datapump_jobs; <– no rows, 모두 삭제됐다.

#6 여러 사용자의 테이블 한꺼번에 expdp 받기
$expdp system/oracle directory=datapump dumpfile=scott16.dmp tables=scott.emp, hr.departments

#7 병렬로 expdp 작업하기
$expdp system/oracle full=y directory=datapump dumpfile=full04.dmp job_name=a parallel=4 <–top -c로 확인

$expdp system/oracle full=y parallel=4 dumpfile=datadir1:full1%U.dat, datadir2:full2%U.dat, datadir3%U.dat, datadir4%U.dat filesize=100m
datadir1~4 mkdir및 driectory 추가 및 grant 되어있어야함

#9 파라미터 파일 사용해서 expdp 수행 – 여러 개의 파일로 분할 expdp
$vi expdp_pump.par

$expdp parfile=expdp_pump.par

4. impdp 사용하기

#1 parameter 파일 이용해서 impdp 작업하기 
$vi impdp.par
$impdp parfile=impdp.par

#2 impdp 병렬 작업하기
$impdp system/oracle parallel=4 dumpfile=datadir1:full1%U.dat, datadir2:full2%U.dat, datadir3%U.dat, datadir4%U.dat table_exists_action=append

#3 import 수행하지 않고 DDL 문장만 추출하기
$impdp system/oracle directory=datapump dumpfile=expdp_%U.dmp sqlfile=datapump.dat

#4 작업 예상시간 추출하기
select sid,serial#,sofar,totalwork from v$session_longops where opname=’DATAPUMP’ and sofar !=totalwork;
                                                                                                               job_name 대문자 입력

#5 데이터 펌프 재 설치하기 (10.2 이상 버전)


#6 설정된 directory 경로 확인하기
set line 200
col owner for a10
col directory_name for a25
col directory_path for a60
select * from dba_directories;





오라클 DBA 박용석 입니다. 으로 문의 주세요.

Datapump – expdp, impdp”의 59개의 댓글

  • 2018년 7월 19일 10:11 오후

    hi!,I like your writing so so much! share we
    communicate more about your post on AOL? I need a specialist
    on this house to unravel my problem. May be that’s you! Looking forward to see you.

  • 2018년 7월 20일 12:41 오전

    Hey are using WordPress for your blog platform? I’m new
    to the blog world but I’m trying to get started and set up my own.
    Do you need any html coding expertise to make your own blog?
    Any help would be really appreciated!

  • 2018년 7월 20일 10:05 오후

    Thanks. Argh! I want more designers made bigger earrings. And as
    soon as harvested, gold is among the most versatile metals on earth.

  • 2018년 7월 21일 3:09 오전

    Additionally, the type of changes needed in loosening
    too-tight clip on earrings relies on the type of attachment on the earring.

  • 2018년 7월 21일 3:12 오전

    The form of our fingers can be a factor as some individuals have extra prominent knuckles.
    The official Prague beer!

  • 2018년 7월 21일 1:58 오후

    four. To stop the posts on earrings from bending simply, don’t apply lots of stress to them when putting them in your ears.

  • 2018년 7월 21일 2:14 오후

    Properly, aren’t these just cute as a button! Enamel bangle bracelets and
    brooches are created by esteemed designers. Really nice ruby and gold

  • 2018년 7월 21일 2:15 오후

    These 5 things will definitely improve your brain.

  • 2018년 7월 21일 2:19 오후

    Matching earrings are also included in the set.
    Go for gold on this season’s assertion pieces to finish off your look.

  • 2018년 7월 21일 2:20 오후

    Albert confirmed some discomfort. It’s a leafy and inexperienced space of Central London and is picturesque in the summertime time.

  • 2018년 7월 21일 2:22 오후

    Make the most of a cloth dampened using an answer of water as well as some falls of
    meal-washing liquid to fully clean your pearls.

  • 2018년 7월 21일 2:24 오후

    Decision making on your self is wonderful as a result of it
    suits your wants. Choices made by others for you aren’t at
    all times in your finest pursuits.

  • 2018년 7월 21일 3:13 오후

    Here on my Handmade Beaded Jewelry web site I supply individuals unique and one-of-a-type necklace and earring units!

  • 2018년 7월 21일 3:18 오후

    The woodland is generally coniferous as it is a commercial plantation, however this area is combined, so
    attracts a wide variety of birds.

  • 2018년 7월 21일 3:19 오후

    Maintaining prior to the war on Search Engine
    Optimization search engine marketing, Googles Rules and what realy works getting greater regarding the machines.

  • 2018년 7월 21일 3:35 오후

    But this could not help keep you off from
    planning a cross social relationship. on Google.
    Plus my own individual web sites are This simple design has save a huge number of individuals
    from losing a hand in these machines. Why would the operator stick their hand in such a dangerous machine?

  • 2018년 7월 21일 4:17 오후

    To those who live near the Seaside Line in Yokohama, Kangawa-ken, check
    out ideas to be more spendthrift.

  • 2018년 7월 21일 4:38 오후

    Wow, I didn’t know there’re a lot of benefits to playing stupid!

  • 2018년 7월 21일 5:18 오후

    The fact of matter is, this documentary ended up being just average PBS quality, and it
    was not stellar.

  • 2018년 7월 21일 6:10 오후

    We use Grapeshot as a contextual targeting technology, enabling united states generate custom sets
    of stories outside out of our usual site navigation.

  • 2018년 7월 21일 11:44 오후

    , the place the place Jesus was crucified. Take two earring mounts with as massive mounting pads as possible and select two items of creste di galli

  • 2018년 7월 21일 11:53 오후

    If you are a picky buyer, you’ll certainly love the offerings acquired.
    Now I’m going to let you in on what I have found.

  • 2018년 7월 22일 12:06 오전

    If you’re beginning to study jewellery making, you would possibly postpone your start date as you consider you need
    a whole lot…

  • 2018년 7월 22일 12:48 오전

    As an example, if the partner does not wish for one to
    pry to their past, cannot get behind their straight back and hire a detective to sniff around.

  • 2018년 7월 22일 1:33 오전

    The simplest and most controllable heat supply to work
    with is a butane torch, which is similar to the kind utilized in kitchens.

  • 2018년 7월 22일 8:51 오전

    Ahaa, its nice discussion concerning this paragraph here at this website, I have read all that, so now me also commenting here.

  • 2018년 7월 22일 10:28 오전

    Some are corrupted by news, at least in some of these manifestations.

  • 2018년 7월 22일 10:41 오전

    An eight hour change can become a nine hour change as most side
    work wasn’t permitted throughout the shift like
    rolling silverware.

  • 2018년 7월 22일 10:46 오전

    I don’t understand… easily had been being chased I would personallyn’t lock myself in a room upstairs, i might run outside!

  • 2018년 7월 22일 10:54 오전

    Thanks for your personal marvelous posting! I seriously enjoyed reading it, you’re a great
    author.I will be sure to bookmark your blog and definitely
    will come back down the road. I want to encourage you to definitely continue your great writing, have a nice weekend!

  • 2018년 7월 22일 10:56 오전

    Whereas on retail version you will have the commentary individuals
    are familiar with getting on the DVD’s and a host of other features.

  • 2018년 7월 22일 11:03 오전

    It had been like the claims he made had been true
    simply because he said them.

  • 2018년 7월 22일 11:26 오전

    Online is enjoyable, addicting and packed with things
    you didn’t know existed!

  • 2018년 7월 31일 4:48 오후

    What’s up, for all time i used to check web site posts here in the early hours in the daylight,
    as i like to learn more and more.

  • 2018년 8월 3일 7:09 오후

    Howdy! Someone in my Facebook group shared this site with us so I came to look it over.
    I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers!
    Superb blog and terrific design and style.

  • 2018년 8월 4일 7:09 오전

    Mommʏ and DaԀdy hugged thе twins beсause it was getting time to get to bed.
    ?Mommy thinks the most effective thing about God is he ցave me these two little rascals аnd
    they are the best factor in Mοmmy?ѕ world.? She said cuddling and
    tickling both boys. That was the sort of thing mommies аll the time say.
    The giggleԁ and hugged Mommy and were virtᥙally able to
    go to their bunk beds when Lee saіd.

  • 2018년 8월 6일 8:45 오전

    Everyone loves your site.. great colorations & theme. Would anyone style and design this website yourself as well as does you actually hire an attorney to make it happen for you personally? Plz reply as I!|m seeking to style my own, personal weblog and would wish to know where ough obtained that out of. many thanks

  • 2018년 8월 14일 3:49 오후

    This helps make skin look more hydrated and youthful, since dried-out skin can wrinkle
    quicker and may look older far more quickly. They break us down over time by attacking our cells, in fact it
    is only by making use of antioxidants that their damage gets repaired.
    People have theorized when we might eat bee propolis then we will eliminating the germs and things, which try to kill us.

  • 2018년 8월 15일 2:13 오전

    Many of these shows are situated in bigger cities like New York or Los Angeles, which means you get
    to travel at no cost if you achieve in to the finals.
    ” It was President Theodore Roosevelt who had given it the category of White House in 1901. You need a special connector typically termed as a Fire wire or best known as a possible IEEE 1394 high band connector.

  • 2018년 8월 15일 5:05 오전

    I’m gone to say to my little brother, that he should also go to see this web site on regular basis
    to get updated from most up-to-date news.

  • 2018년 8월 15일 11:12 오전

    We set boundaries on land to create web-sites what-only, typically anyway, we do
    not use piss as markers. Most people believe cats usually do this
    with greater regularity if they are experiencing certain kinds of stress.
    Remember to ensure your cats are sterilized because this
    solves most issues of spraying.

  • 2018년 8월 16일 5:34 오전

    I am delighted that I detected this website, exactly the right info that
    I was looking for!

  • 2018년 8월 19일 3:20 오후

    This is a good tip especially to those fresh to the blogosphere.
    Brief but very accurate information… Appreciate your sharing this
    one. A must read post!

  • 2018년 8월 20일 7:48 오후

    This excellent website truly has all of the info I needed about this subject
    and didn’t know who to ask.

  • 2018년 8월 22일 1:56 오후

    Howdy! Do you know if they make any plugins to assist with SEO?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very
    good success. If you know of any please share. Appreciate it!

  • 2018년 8월 22일 10:38 오후

    I am really grateful to the holder of this web page who has shared this wonderful piece of writing at at this time.

  • 2018년 8월 22일 10:46 오후

    Wow, that’s what I was exploring for, what a material! present here
    at this webpage, thanks admin of this website.

  • 2018년 8월 24일 4:37 오전

    I’m not sure why but this weblog is loading incredibly slow for me.
    Is anyone else having this issue or is it a issue on my end?
    I’ll check back laater on and see if the problem still exists.

  • 2018년 8월 24일 11:52 오전

    Hurrah! After all I got a blog from where I be capable of truly get valuable information concerning
    my study and knowledge.

  • 2018년 8월 24일 6:11 오후

    Thank you for some other informative web site.
    Where else could I am getting that type of info written in such a perfect approach?
    I have a venture tha I am just now working on, and I have been att the look out foor such info.

  • 2018년 8월 25일 6:46 오전

    Amazing! Its genuinely amazing paragraph, I have got much
    clear idea regarding from this piece of writing.

  • 2018년 8월 25일 1:29 오후

    Hi to every body, it’s my first pay a quick visit of
    this blog; this web site carries awesome and really good material for readers.

  • 2018년 8월 27일 12:40 오전

    I do not even understand how I finished up right here, but I believed this submit used to
    bbe good. I do not rechognize who you might be but
    certainly you’re going to a well-known blogger when you aren’t already.

  • 2018년 8월 27일 1:11 오전

    Hi there, I would like to subscribe for this
    weblog to obtain most recent updates, therefore where can i do it please help.

댓글이 닫혀있습니다.