Re: How do you refresh your databases?

From: Adric Norris <landstander668_at_gmail.com>
Date: Wed, 23 Mar 2011 09:18:21 -0600
Message-ID: <AANLkTinRPndboo8=+V+De=ADsdyzvP6OFmqnWcrP3p8M_at_mail.gmail.com>

Just curious... what's the advantage of driving Datapump from PL/SQL, rather than the command line client?

On Wed, Mar 23, 2011 at 8:15 AM, Goulet, Richard <Richard.Goulet_at_parexel.com
> wrote:

> Joel,
>
> The following works for me:
>
> declare
> h1 NUMBER := 0;
> h2 varchar2(1000);
> ex boolean := TRUE;
> fl number := 0;
> link varchar2(100) := 'DBSRC.US';
> schema_exp varchar2(1000) := 'in(''<user1>'',''<user2>'')';
> schema varchar2(100) := 'refresh';
> blksz number := 0;
> SUCCESS_WITH_INFO exception;
> begin
> utl_file.fgetattr('DATA_PUMP_DIR', schema||'.log', ex, fl, blksz);
> if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',schema||'.log');
> end if;
> h1 := dbms_datapump.open (operation => 'IMPORT', job_mode =>
> 'SCHEMA', remote_link => link, job_name => upper(schema)||'_EXP', version =>
> 'COMPATIBLE');
> dbms_datapump.set_parallel(handle => h1, degree => 6);
> dbms_datapump.add_file(handle => h1, filename => schema||'.log',
> directory => 'DATA_PUMP_DIR', filetype => 3);
> dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value
> => 0);
> dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA',
> value => 1);
> dbms_datapump.set_parameter(handle => h1, name =>
> 'TABLE_EXISTS_ACTION', value=>'SKIP');
> dbms_datapump.metadata_filter(handle=>h1,
> name=>'SCHEMA_EXPR',value=>schema_exp);
> dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step =>
> 0);
> dbms_datapump.wait_for_job(handle=>h1, job_state=>h2);
> exception
> when SUCCESS_WITH_INFO THEN NULL;
> when others then
> h2 := sqlerrm;
> if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
> end if;
> dbms_output.put_line(h2);
> end;
>
>
> Dick Goulet
> Senior Oracle DBA/NA Team Leader
>

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

Received on Wed Mar 23 2011 - 10:18:21 CDT