Re: expdp and impdp batch script for windows

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Wed, 6 Nov 2013 13:54:27 +1100
Message-ID: <CAFeFPA9UU1egRUzn4rUDKO6tQZTPh65isiyWGLbmwEUpWdv51A_at_mail.gmail.com>

Why a windows batch script?

You can create a stored procedure and schedule it via the database scheduler

something very basic can look something like this

CREATE OR REPLACE procedure imp_test_schema as

  l_dp_handle       NUMBER;
  l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
  l_job_state       VARCHAR2(30) := 'UNDEFINED';
  l_sts             KU$_STATUS;

BEGIN
  l_dp_handle := DBMS_DATAPUMP.open(

    operation   => 'IMPORT',
    job_mode    => 'SCHEMA',
    remote_link => 'dbatest', -- database link needs to be created in the

database before the procedure is run

    job_name => 'IMPDP_TEST',
    version => 'LATEST');

  DBMS_DATAPUMP.add_file(

    handle    => l_dp_handle,
    filename  => 'IMPDP_SCHEMA.log',
    directory => 'DATA_PUMP_DIR',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

   DBMS_DATAPUMP.SET_PARAMETER (

    handle    => l_dp_handle,
    name      => 'TABLE_EXISTS_ACTION',
    value     => 'REPLACE');

  DBMS_DATAPUMP.metadata_filter(

    handle => l_dp_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= ''TEST'''); -- Schema TEST only

   DBMS_DATAPUMP.DATA_FILTER(
     handle => l_dp_handle,
     name => 'INCLUDE_ROWS',
     value => 0); --1 will include rows and is default

    DBMS_DATAPUMP.metadata_filter
    (handle => l_dp_handle,
    name => 'EXCLUDE_PATH_EXPR',
    VALUE => '=''OBJECT_GRANT'''); -- excludes object grants

  DBMS_DATAPUMP.start_job(l_dp_handle);

  DBMS_DATAPUMP.detach(l_dp_handle);
END;
/

Jack van Zanen

---

This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation

On Tue, Nov 5, 2013 at 11:56 PM, Nagaraj S <nagaraj.chk_at_gmail.com> wrote:

> Good Morning Gurus,
>
> Please share me windows batch script to automate the export and import
> activity in oracle 11.2.0.3 version. I need to take export of a table daily
> and import to table of different database in different server.
>
> -Naga
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 06 2013 - 03:54:27 CET