In this example, DUMMY is a database link to the source system which is also the schema name in the source system. PUMPY is the schema name in the local database where we want to create the sequences.
DECLARE
l_dp_handle NUMBER;
v_job_state varchar2(4000);
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(operation => 'IMPORT',
job_mode => 'SCHEMA',
remote_link => 'DUMMY',
version => 'LATEST');
DBMS_DATAPUMP.add_file(handle => l_dp_handle,
filename => 'test.log',
directory => 'DATA_PUMP_DIR',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
reusefile => 1);
DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''DUMMY''');
DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
'INCLUDE_PATH_EXPR',
'IN (''SEQUENCE'')');
DBMS_DATAPUMP.METADATA_REMAP(l_dp_handle,
'REMAP_SCHEMA',
'DUMMY',
'PUMPY');
DBMS_DATAPUMP.start_job(l_dp_handle);
DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);
DBMS_OUTPUT.PUT_LINE(v_job_state);
END;
hii Harrison
ReplyDeleteI am exporting tables along with the sequence by following code
declare
h1 number;
v_schema VARCHAR2(32);
v_job_state varchar2(4000);
v_time varchar2(32);
begin
SELECT to_char(SYSDATE, 'DD_MM_YYYY_hh24_mi_ss') INTO v_time FROM DUAL;
v_schema:='KNK';
h1 := dbms_datapump.open(operation => 'EXPORT', job_mode => 'SCHEMA');
DBMS_DATAPUMP.add_file (h1, v_schema||'_'||v_time||'.DMP','EXP',reusefile => 1);
DBMS_DATAPUMP.add_file (handle => h1,filename => v_schema||'_'||v_time||'.LOG',directory => 'EXP1',reusefile => 1,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.metadata_filter(handle => h1,name => 'SCHEMA_EXPR',value => '= ''KNK''');
dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''T_VISIT'',''T_SUBVISIT'',''T_SERVICES'')', object_type => 'TABLE');
dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''PATIENT_INFO_SEQ'',''PATIENT_SERVICES_REG2_SEQ'')', object_type => 'SEQUENCE');
dbms_datapump.start_job(h1);
DBMS_DATAPUMP.WAIT_FOR_JOB(h1, v_job_state);
DBMS_OUTPUT.PUT_LINE(v_job_state);
end;
-------------
i want to import(table exists action replace) the same exported file( tables & sequnces together)
Hi Umair,
ReplyDeleteAs the filtering is all done on the export you don't have to worry about any of that - you just need to do a full schema import the only additional thing being the table_exists_action of replace. You should be able to find a basic example doing that via a google search.
Cheers,
Rich
Hello Harrison Thank you so much for reply.
ReplyDeleteI am getting the following error while importing with this code
declare
h1 number;
v_schema VARCHAR2(32);
v_job_state varchar2(4000);
v_time varchar2(32);
begin
SELECT to_char(SYSDATE, 'DD_MM_YYYY_hh24_mi_ss') INTO v_time FROM DUAL;
v_schema:='KNK2';
h1 := dbms_datapump.open(operation => 'IMPORT', job_mode => 'TABLE');
DBMS_DATAPUMP.add_file (h1,filename => 'KNK2_03_05_2017_14_42_02.DMP',directory => 'EXP_DIR',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
DBMS_DATAPUMP.add_file (handle => h1,directory => 'EXP_DIR',filename => 'IMPapi1.log',filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.metadata_filter(handle => h1,name => 'SCHEMA_EXPR',value => '= ''KNK2''');
dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''T_VISIT'',''T_SUBVISIT'',''T_SERVICES'')', object_type => 'TABLE');
dbms_datapump.metadata_filter(handle => h1,name => 'NAME_EXPR',value => 'IN (''PATIENT_INFO_SEQ'',''PATIENT_SERVICES_REG2_SEQ'')', object_type => 'SEQUENCE');
dbms_datapump.set_parameter (h1, 'TABLE_EXISTS_ACTION', 'REPLACE');
dbms_datapump.start_job(h1);
DBMS_DATAPUMP.WAIT_FOR_JOB(h1, v_job_state);
DBMS_OUTPUT.PUT_LINE(v_job_state);
end;
Error
ReplyDeleteORA-39002: invalid operation