Re: expdp and impdp batch script for windows
- From: Jack van Zanen <jack@xxxxxxxxxxxx>
- To: Nagaraj S <nagaraj.chk@xxxxxxxxx>
- Date: Wed, 6 Nov 2013 13:54:27 +1100
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@xxxxxxxxx> 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
>
>
Other related posts:
