Re: How do you refresh your databases?
- From: Adric Norris <landstander668@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 23 Mar 2011 09:18:21 -0600
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@xxxxxxxxxxx
> 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
>
Other related posts:
