Dbms datapump 데이터 필터링
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 [DBMS_DATAPUMP api] TABLE DDL 반영 - METADATA_ONLY 처리[편집]
PROCEDURE export_data(p_directory IN VARCHAR2) IS
CURSOR c_norows IS
select owner, table_name from dba_tables WHERE owner in (
select distinct owner from reload_dev_tables) and status='VALID' and temporary = 'N'
and secondary = 'N' and nested = 'NO' and dropped = 'NO' and iot_name is null
minus
select owner, table_name from reload_dev_tables;
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
s varchar2(3000);
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'dev_dw.dmp',
directory => p_directory);
DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'dev_dw.log',
directory => p_directory,
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
--
SELECT listagg(''''||owner||'''', ', ') WITHIN GROUP (ORDER BY owner) INTO s
FROM (SELECT DISTINCT owner FROM dba_tables WHERE owner IN (SELECT distinct owner from reload_dev_tables));
DBMS_DATAPUMP.metadata_filter(l_dp_handle, 'SCHEMA_LIST', s);
-- Add query filters
FOR rec IN (SELECT owner, table_name, filter_predicate FROM reload_dev_tables r
WHERE filter_predicate IS NOT NULL AND
EXISTS (SELECT 1 FROM dba_tables t WHERE t.owner = r.owner AND t.table_name = r.table_name
AND t.dropped = 'NO') ) LOOP
DBMS_DATAPUMP.DATA_FILTER (
handle => l_dp_handle,
name => 'SUBQUERY',
value => 'WHERE '||rec.filter_predicate,
table_name => rec.table_name,
schema_name => rec.owner);
END LOOP;
-- Add tables without rows
FOR rec IN c_norows LOOP
DBMS_DATAPUMP.DATA_FILTER (
handle => l_dp_handle,
name => 'INCLUDE_ROWS',
value => 0,
table_name => rec.table_name,
schema_name => rec.owner);
END LOOP;
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.detach(l_dp_handle);
END;
2 [DBMS_DATAPUMP api] TABLE DDL 반영 - METADATA_ONLY 처리[편집]
- 테스트용 테이블 생성
SQL> create table t1 (id number, tekst varchar2(10));
SQL> create table t2 (id number, tekst varchar2(10));
SQL> create table t3 (id number, tekst varchar2(10));
SQL> insert into t1 values (1, 'tekst 1');
SQL> insert into t1 values (2, 'tekst 2');
SQL> insert into t2 values (1, 'tekst 1');
SQL> insert into t2 values (2, 'tekst 2');
SQL> insert into t3 values (1, 'tekst 1');
SQL> insert into t3 values (2, 'tekst 2');
SQL> commit;
- [datapump api] dbms_datapump.metadata_filter함수 SCHEMA_LIST , NAME_LIST 이용
declare
h1 NUMBER;
l_status varchar2(2000);
begin
h1 := dbms_datapump.open(operation => 'EXPORT', job_mode => 'TABLE', job_name => 'TBL_EXP_TST');
dbms_datapump.add_file(handle => h1, filename => 't_tst.dmp', directory=> 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
dbms_datapump.add_file(handle => h1, filename => 't_tst.log', directory=> 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => '''DBCAFE''');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST', value => '''T1'',''T2'',''T3''');
dbms_datapump.start_job(handle => h1);
dbms_datapump.wait_for_job( handle => h1, job_state => l_status );
dbms_datapump.detach(handle => h1);
exception when others then
dbms_datapump.detach(handle => h1);
raise;
end;
/
- SUBQUERY 사용
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_LIST', value => '''LJ''');
dbms_datapump.metadata_filter(handle => h1, name => 'NAME_LIST', value => '''T1'',''T2'',''T3''');
dbms_datapump.data_filter(handle => h1, table_name=>'T1', schema_name=>'LJ', name => 'SUBQUERY', value => 'WHERE id = 1');
dbms_datapump.data_filter(handle => h1, table_name=>'T2', schema_name=>'LJ', name => 'SUBQUERY', value => 'WHERE id = 2');