Oracle 데이터펌프
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 DATAPUMP 데이터펌프[편집]
- 권한 생성
- 디렉토리 권한 부여
- 디비링크 생성 후 디비링크를 통한 데이터 펌프 실행
2 IMPDP (Import Datapump) 사용법[편집]
3 EXPDP (Export Datapump) 사용법[편집]
4 데이터펌프 작업 관리 및 모니터링[편집]
4.1 ExportDP JOB 확인[편집]
4.1.1 터미널에서 JOB Attach 모드[편집]
expdp 아이디/비밀번호@db명 attach=JOB이름
expdp>status
4.1.2 SQL로 확인[편집]
- dba_datapump_jobs
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;
- sys.v_$sqlarea
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name
, rows_processed
, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
4.2 Datapump 작업 진행사항[편집]
SELECT X.JOB_NAME
, B.STATE
, B.JOB_MODE
, B.DEGREE
, X.OWNER_NAME
, Z.SQL_TEXT
, P.MESSAGE
, P.TOTALWORK
, P.SOFAR
, ROUND ( (P.SOFAR / P.TOTALWORK) * 100, 2) DONE
, P.TIME_REMAINING
FROM DBA_DATAPUMP_JOBS B
LEFT JOIN DBA_DATAPUMP_SESSIONS X ON (X.JOB_NAME = B.JOB_NAME)
LEFT JOIN V$SESSION Y ON (Y.SADDR = X.SADDR)
LEFT JOIN V$SQL Z ON (Y.SQL_ID = Z.SQL_ID)
LEFT JOIN V$SESSION_LONGOPS P ON (P.SQL_ID = Y.SQL_ID)
-- WHERE Y.MODULE = 'Data Pump Worker' AND P.TIME_REMAINING > 0
;
5 데이터펌프(DATAPUMP) JOB 중지[편집]
SELECT * FROM DBA_DATAPUMP_JOBS;
5.1 IMPDP ATTACH=JOB 접속 후[편집]
$>IMPDP SCOTT/TIGER@DB ATTACH=JOB명
KILL_JOB
STOP_JOB #STOP_JOB = IMMEDIATE 즉시 종료
5.2 API 이용 정지[편집]
- 주의) 즉시 정지 하지 않음
DECLARE
h1 NUMBER;
BEGIN
h1:=DBMS_DATAPUMP.ATTACH(JOB_NAME => '"MIG_CHANGED_TABLE"', JOB_OWNER => 'RTIS_MIG');
DBMS_DATAPUMP.STOP_JOB(h1,1,0);
END;
6 EXPORT API[편집]
6.1 스키마 EXPORT[편집]
DECLARE
hndl NUMBER;
TAG_NAME VARCHAR2(30) := 'EXP_MIG_TEST01';
BEGIN
hndl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT'
, job_mode => 'SCHEMA' -- FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE
, job_name=>'JOB_'||TAG_NAME
--, remote_link => 'DBLINK_NAME', version => 'LATEST'
);
DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => TAG_NAME||'.dmp', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE, reusefile=>1);
DBMS_DATAPUMP.ADD_FILE( handle => hndl, filename => TAG_NAME||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
-- DBMS_DATAPUMP.SET_PARAMETER(handle=> hndl, name=> 'INCLUDE_METADATA', value=> 1); -- META 포함여부
DBMS_DATAPUMP.DATA_FILTER(handle=> hndl, name=> 'INCLUDE_ROWS', value=> 0); -- DATA 포함 여부 0,
DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'SCHEMA_EXPR', value=>'IN (''FED40'',''TTT'')');
-- DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'NAME_EXPR', value=>'IN (''FED40'', ''TEST'')');
-- DBMS_DATAPUMP.METADATA_FILTER(handle=> hndl, name=> 'NAME_LIST', value=>'''EMP'',''DEPT''');
DBMS_DATAPUMP.START_JOB(hndl);
END;
6.2 테이블 EXPORT API[편집]
-- 1.테이블 DUMP EXPORT 스크립트
DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>'JOB_EXP_TB_RC_JEJU_14');
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'INIT_BK_TB_RC_JEJU_14_20190910.EXP', directory => 'DATAPUMP2', filetype => dbms_datapump.ku$_file_type_dump_file ,reusefile=>1);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'INIT_BK_TB_RC_JEJU_14_20190910.LOG', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file ,reusefile=>1);
-- 스키마 정보
DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl,
name => 'SCHEMA_EXPR',
value => ' IN (''유저명'')'
);
-- 테이블 정보
DBMS_DATAPUMP.METADATA_FILTER(handle => hdnl
,name => 'NAME_EXPR'
,value => ' IN (''테이블명'')'
,object_type => 'TABLE'
);
DBMS_DATAPUMP.START_JOB(hdnl);
END;
7 데이터펌프 로그파일 읽기 API[편집]
DECLARE
V1 VARCHAR2(200); --32767
F1 UTL_FILE.FILE_TYPE;
BEGIN
F1 := UTL_FILE.FOPEN('DATA_PUMP_DIR','INIT_BK_TB_RC_JEJU_12_20190910.LOG','R');
Loop
BEGIN
UTL_FILE.GET_LINE(F1,V1);
dbms_output.put_line(V1);
EXCEPTION WHEN No_Data_Found THEN EXIT;
END;
end loop;
IF UTL_FILE.IS_OPEN(F1) THEN
dbms_output.put_line('File is Open');
end if;
UTL_FILE.FCLOSE(F1);
END;
8 파일 복사(ASM등)[편집]
attach_file OS상의 파일복사 명령를 DB에서 직접 수행이 가능.
- DBMS_FILE_TRANSFER.COPY_FILE 패키지/함수
- 소스 디렉토리에서 파일을 읽고 대상 디렉토리에 복사
- ASM 과 로컬 디스크간 복사
- 소스 및 대상 디렉토리는 로컬 파일 시스템에 있거나 ASM (Automatic Storage Management) 디스크 그룹에 있거나
로컬 파일 시스템과 ASM간에 어느 방향 으로든 복사 할 수 있음.
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'SOURCEDIR' -- 소스 디렉토리
, source_file_name => 't_xdbtmp.f' -- 소스 파일
, destination_directory_object => 'DGROUP' -- 타켓 디렉토리
, destination_file_name =>'t_xdbtmp.f' -- 타겟 파일
);
END;
/
8.1 DB LINK를 이용한 파일 복사(밀어 넣기)[편집]
attach_file DBMS_FILE_TRANSFER.PUT_FILE 패키지/함수
- 소스 에서 프로시져 실행 해야 함.
- 소스 => 타겟으로 복사
- 로컬 파일 또는 ASM을 읽고 원격 데이터베이스에 접속하여 원격 파일 시스템에 파일 사본을 작성합니다.
- DB링크 간의 DUMP파일 복사가 가능(.log파일은 복사 안됨,12c)
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DATA_PUMP_DIR', -- 패키지 실행하는 DB
source_file_name => 'sample.dmp', -- 패키지 실행하는 DIRECTORY의 덤프파일명
destination_directory_object => 'DATA_PUMP_DIR2', -- DB링크 원격지 디렉토리
destination_file_name => 'sample_copied.dmp',-- DB링크 원격지에 저장될 파일명
destination_database => '디비링크명'
);
END;
/
8.2 파일 복사(가져오기)[편집]
attach_file DBMS_FILE_TRANSFER.GET_FILE
- 원격 데이터베이스에 접속하여 원격 파일을 로컬 파일 시스템 또는 ASM에 파일 복사.
- 절차가 성공적으로 완료 될 때까지 대상 파일이 닫히지 않음.
BEGIN
DBMS_FILE_TRANSFER.GET_FILE(
source_directory_object => 'DATA_PUMP_DIR',
source_file_name => 'sample.dmp',
source_database => '디비링크명',
destination_directory_object => 'DATA_PUMP_DIR2',
destination_file_name => 'sample_copied.dmp'
);
END;
/
9 DATAPUMP API 상세 정보[편집]
https://www.morganslibrary.org/reference/pkgs/dbms_datapump.html
10 DB LINK를 이용한 파일 복사[편집]
BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DATA_PUMP_DIR',
source_file_name => 'sample.dmp',
destination_directory_object => 'DATA_PUMP_DIR',
destination_file_name => 'sample_copied.dmp',
destination_database => 'to_rds'
);
END;
/