DBMS_DATAPUMP import of only sequences including remap_schema



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;

Comments

  1. Anonymous2 May 2017 at 22:05

    hii Harrison

    I 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)

    ReplyDelete
  2. DBAHarrison3 May 2017 at 00:08

    Hi Umair,
    As 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

    ReplyDelete
  3. Anonymous3 May 2017 at 02:21

    Hello Harrison Thank you so much for reply.

    I 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;

    ReplyDelete
  4. Anonymous3 May 2017 at 02:45

    Error

    ORA-39002: invalid operation

    ReplyDelete

Post a Comment