[오라클]DataPump Job

Data Pump (Master Table)

      1) External Table 확장
      2) expdp, impdp
      * DB Link를 이용한 Network Mode (NETWORK_LINK 옵션)
      * Directory Object가 반드시 필요(DIRECTORY 옵션)
      * expdp 옵션에 의한 객체 단위 포함/제외 가능(EXCLUDE/INCLUDE 옵션 및 향상된 QUERY 옵션)
      * 제한적이긴 하지만 Parallel 가능(PARALLEL 옵션)
      * Master Table에 의한 중지 작업 재시작 및 작업 변경(추가 및 삭제)이 가능
      * REMAP_SCHEMA, REMAP_DATAFILE, REMAP_TABLESPACE 등 새로운 옵션

## 실습 01 ######################################################################

sqlplus system/oracle  또는 conn system/oracle

host mkdir C:\oracle10g\EXP_PUMP1
host mkdir C:\oracle10g\EXP_PUMP2

CREATE OR REPLACE DIRECTORY exp_pump_dir1 AS 'C:\oracle10g\EXP_PUMP1' ;
CREATE OR REPLACE DIRECTORY exp_pump_dir2 AS 'C:\oracle10g\EXP_PUMP2' ;

grant read, write on directory exp_pump_dir1 to public ;
grant read, write on directory exp_pump_dir2 to public ;

exit

cd c:\oracle10g\exp_pump

1)
expdp hr/hr DUMPFILE=exp_pump_dir1:exp_hr03.dmp LOGFILE=exp_pump_dir2:exp_hr03.log

expdp hr/hr DUMPFILE=exp_pump_dir:expdp_hr01.dmp NOLOGFILE=y

2)                                                                 
expdp hr/hr DIRECTORY=exp_pump_dir1 DUMPFILE=exp_hr02.dmp LOGFILE=exp_pump_dir2:exp_hr02.log

3)
set DATA_PUMP_DIR=exp_pump_dir

expdp system/oracle DUMPFILE=exp_hr_emp01.dmp NOLOGFILE=y tables=hr.employees
expdp system/oracle DUMPFILE=exp_hr05.dmp LOGFILE=exp_hr04.log SCHEMAS=hr

## 실습 02 expdp impdp 기본 옵션 ################################################

(실습 준비)

mkdir c:\oracle10g
mkdir c:\oracle10g\dptest

cd c:\oracle10g\dptest
sqlplus /nolog
conn sys/oracle as sysdba

create directory dp_test_dir as 'c:\oracle10g\dptest' ;

grant read, write on directory dp_test_dir to hr ;

conn hr/hr

create table emps03_02 tablespace example
as
  select * from hr.employees ;

insert into emps03_02 select * from emps03_02 ; --> 12회 정도 실행

commit ;

select sum(bytes)/1024/1024 AS "SIZE_MB" from user_segments
where  segment_name = 'EMPS03_02' ;

conn system/oracle

alter system checkpoint ;

exec dbms_stats.gather_table_stats('HR','EMPS03_02' -
     ,estimate_percent => dbms_stats.auto_sample_size -
     ,method_opt => 'FOR ALL COLUMNS SIZE AUTO' , cascade => TRUE) ;

select sum(bytes)/1024/1024 AS "SIZE_MB" from dba_segments
where  owner='HR' and segment_name = 'EMPS03_02' ;

exit

------------------------------------------------------------------------
## exp_test01_par01.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest01
logfile=exp_test01.log
filesize=2M
tables=emps03_02
estimate=statistics
estimate_only=y           <- 실제로 익스포트 안받고 확인받하는거야? 사용공간 분석이야

- 아래 애는 절대경로를 사용한다..
expdp parfile=exp_test01_par01.txt

------------------------------------------------------------------------
## exp_test01_par02.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest0102
logfile=exp_test0102.log
filesize=5M
tables=emps03_02
dumpfile=exp_test02_%U.dmp

expdp parfile=exp_test01_par02.txt

------------------------------------------------------------------------
## imp_test01_par03.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=imptest0103
logfile=imp_test0103.log
tables=emps03_02
dumpfile=exp_test02_%U.dmp
table_exists_action=append

sqlplus system/oracle
truncate table hr.emps03_02 ;
exit

impdp parfile=imp_test01_par03.txt

## exp_test01_par04.txt  파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest0104
logfile=exp_test0104.log
filesize=5M
dumpfile=exp_test04_%U.dmp
EXCLUDE=VIEW
EXCLUDE=PACKAGE
EXCLUDE=INDEX:"LIKE 'EMP_%'"
EXCLUDE=TABLE:"LIKE 'EMPS03%'"
EXCLUDE=TABLE:"LIKE 'JOB%'"
QUERY=HR.employees:"WHERE department_id IN (10,20) and salary<1600"

expdp parfile=exp_test01_par04.txt

(참고)
CONTENT=METADATA_ONLY
CONTENT=DATA_ONLY
CONTENT=ALL

 

## 실습 03 Attach & JOB STOP & JOB RESTART ######################################

(준비작업)

sqlplus hr/hr

select sum(bytes)/1024/1024 AS "SIZE_MB" from user_segments
where  segment_name = 'EMPS03_02' ;

insert into emps03_02 select * from emps03_02 ; <--2회 실행

commit ;

conn system/oracle

alter system checkpoint ;

exit

## 작업내용 확인(현재 진행중이거나, 에러 후 중단된 작업내역을 확인가능.)

sqlplus /nolog
conn sys/oracle as sysdba

grant select on dba_datapump_jobs to hr ; <--조회 권한할당

conn hr/hr

desc dba_datapump_jobs

col OWNER_NAME format a10
col JOB_NAME format a15
col OPERATION format a15
col JOB_MODE format a10
col STATE format a15
set linesize 120

select * from dba_datapump_jobs ;

===================================================================

(SESSION 1)--------------------------------------------------------

## exp_test02_par01.txt 파일 작성

userid=hr/hr
directory=dp_test_dir
job_name=exptest0201
logfile=exp_test0201.log
filesize=30M
tables=emps03_02
dumpfile=exp_test0201_%U.dmp

sqlplus hr/hr

(SESSION 2)--------------------------------------------------------
cd c:\oracle10g\dptest
expdp parfile=exp_test02_par01.txt

(SESSION 1) 작업확인-----------------------------------------------

select * from dba_datapump_jobs ;  <--JOB_NAME 필드 확인

(SESSION 3) <--현재 작업중인 export에 연결.------------------------

expdp hr/hr attach=exptest0201  <--job이름

Export>

(SESSION 1)--------------------------------------------------------

select * from dba_datapump_jobs ;  <--ATTACHED_SESSIONS 수가 증가

(SESSION 3) 현재 작업 중단-----------------------------------------

Export> stop_job

yes 입력 --> 시간걸림 그동안에 다음 수행

(SESSION 1)

select * from dba_datapump_jobs ;  <--ATTACHED_SESSIONS 수가 감소

그 동안에 (SESSION 3) 중단 작업 완료
   --> 원래 SESSION 2도 중단됨

(SESSION 2) 강제 종료

(SESSION 1)

select * from dba_datapump_jobs ;  <-- NOT RUNNING

===================================================================
## 중단작업에 attach 및 작업 재시작 실습
===================================================================

(SESSION 3)

expdp hr/hr attach=exptest0201  <--job이름

Export>

(SESSION 1)

select * from dba_datapump_jobs ;

(SESSION 3) 중단 작업 재개

Export> start_job

Export> status=10  <-- 매 10초간 상태 확인

Export> continue_client <-- Session 2에서 시작된 작업인데 클라이언트가 종료되었으므로
                            Session 3에서 진행 결과를 받도록 설정.

작업완료 후에

(SESSION 1)

select * from dba_datapump_jobs ;

 

 

DBA_DATAPUMP_JOBS

>DBA_DATAPUMP_JOBS identifies all active Data Pump jobs in the database, regardless of their state, on an instance (or on all instances for Real Application Clusters). It also show all Data Pump master tables not currently associated with an active job.

Related View

>USER_DATAPUMP_JOBS displays the Data Pump jobs owned by the current user. This view does not display the OWNER_NAME column.

Column	Datatype	NULL	Description
OWNER_NAME	VARCHAR2(30)		User that initiated the job
JOB_NAME	VARCHAR2(30)		User-supplied name for the job (or the default name generated by the server)
OPERATION	VARCHAR2(30)		Type of job
JOB_MODE	VARCHAR2(30)		Mode of job
STATE	VARCHAR2(30)		Current job state
DEGREE	NUMBER		Number of worker processes performing the operation
ATTACHED_SESSIONS	NUMBER		Number of sessions attached to the job
DATAPUMP_SESSIONS	NUMBER		Number of Data Pump sessions participating in the job