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
Export>status
#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로 확인
#8
$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
userid=system/oracle
directory=datapump
job_name=datapump
logfile=expdp.log
dumpfile=expdp_%U.dmp
filesize=100M
full=y
:wq!
$expdp parfile=expdp_pump.par
4. impdp 사용하기
#1 parameter 파일 이용해서 impdp 작업하기
$vi impdp.par
userid=system/oracle
directory=datapump
job_name=datapump
logfile=impdp_pump.log
dumpfile=expdp_%U.dmp
full=y
table_exists_action=append
:wq!
$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 이상 버전)
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
#6 설정된 directory 경로 확인하기
set line 200
col owner for a10
col directory_name for a25
col directory_path for a60
select * from dba_directories;
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.
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!
Thanks. Argh! I want more designers made bigger earrings. And as
soon as harvested, gold is among the most versatile metals on earth.
Have actually been taking little over a month.
Additionally, the type of changes needed in loosening
too-tight clip on earrings relies on the type of attachment on the earring.
The form of our fingers can be a factor as some individuals have extra prominent knuckles.
The official Prague beer!
four. To stop the posts on earrings from bending simply, don’t apply lots of stress to them when putting them in your ears.
Properly, aren’t these just cute as a button! Enamel bangle bracelets and
brooches are created by esteemed designers. Really nice ruby and gold
necklace.
These 5 things will definitely improve your brain.
Matching earrings are also included in the set.
Go for gold on this season’s assertion pieces to finish off your look.
We’d naturally respect them as individuals.
Albert confirmed some discomfort. It’s a leafy and inexperienced space of Central London and is picturesque in the summertime time.
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.
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.
Here on my Handmade Beaded Jewelry web site I supply individuals unique and one-of-a-type necklace and earring units!
The woodland is generally coniferous as it is a commercial plantation, however this area is combined, so
attracts a wide variety of birds.
Maintaining prior to the war on Search Engine
Optimization search engine marketing, Googles Rules and what realy works getting greater regarding the machines.
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?
Just a stupid person would know.
To those who live near the Seaside Line in Yokohama, Kangawa-ken, check
out ideas to be more spendthrift.
Wow, I didn’t know there’re a lot of benefits to playing stupid!
The fact of matter is, this documentary ended up being just average PBS quality, and it
was not stellar.
We use Grapeshot as a contextual targeting technology, enabling united states generate custom sets
of stories outside out of our usual site navigation.
, 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
pasta.
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.
If you’re beginning to study jewellery making, you would possibly postpone your start date as you consider you need
a whole lot…
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.
The simplest and most controllable heat supply to work
with is a butane torch, which is similar to the kind utilized in kitchens.
Ahaa, its nice discussion concerning this paragraph here at this website, I have read all that, so now me also commenting here.
Some are corrupted by news, at least in some of these manifestations.
An eight hour change can become a nine hour change as most side
work wasn’t permitted throughout the shift like
rolling silverware.
I don’t understand… easily had been being chased I would personallyn’t lock myself in a room upstairs, i might run outside!
I’m sorry, but…” is a complete no-no.
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!
Whereas on retail version you will have the commentary individuals
are familiar with getting on the DVD’s and a host of other features.
It had been like the claims he made had been true
simply because he said them.
Online is enjoyable, addicting and packed with things
you didn’t know existed!
I wager hes PERFECT at it!? Laughed Larry.
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.
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.
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.
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
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.
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.
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.
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.
I am delighted that I detected this website, exactly the right info that
I was looking for!
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!
This excellent website truly has all of the info I needed about this subject
and didn’t know who to ask.
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!
I am really grateful to the holder of this web page who has shared this wonderful piece of writing at at this time.
Wow, that’s what I was exploring for, what a material! present here
at this webpage, thanks admin of this website.
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.
Hurrah! After all I got a blog from where I be capable of truly get valuable information concerning
my study and knowledge.
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.
Amazing! Its genuinely amazing paragraph, I have got much
clear idea regarding from this piece of writing.
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.
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.
Cheers!
Hi there, I would like to subscribe for this
weblog to obtain most recent updates, therefore where can i do it please help.