행위

Oracle 데이터펌프 API활용

DB CAFE

Dbcafe (토론 | 기여)님의 2019년 11월 27일 (수) 13:31 판 (→‎DATAPUMP API 정보)
thumb_up 추천메뉴 바로가기

1 스키마의 메타 동기화[편집]

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;


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;

3 데이터펌프 로그파일 읽기 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;

4 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;
/

5 DATAPUMP API 상세 정보[편집]

https://www.morganslibrary.org/reference/pkgs/dbms_datapump.html

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=Oracle_데이터펌프_API활용&oldid=1910"