다른 명령
[DBMS_DATAPUMP].METADATA_FILTER 함수 - 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;
[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');