Home  >  Oracle Database Administration (DBA)  >  10g DB - New Features  >  Speed up the schema refresh over a DBlink with Data Pump (10g)

1. Create a new user on the target database with DBA role granted

connected as sys run the following commands:

CREATE USER DBA_TOOLS identified by <password>;

grant DBA to DBA_TOOLS; 

2. Create a DBlink in the new schema to point to the source schema

CREATE DATABASE LINK "db_LINK_NAME" 

connect to source_schema

identified by password

using 'DB_alias';

3. Create the procedure which will refresh the data

CREATE OR REPLACE PROCEDURE DBA_TOOLS.copy_schema

          ( source_schema in varchar2,

           destination_schema in varchar2,

           new_password in varchar2 default 'newuser',

           network_link in varchar2 default 'db_LINK_NAME')

as 

 

  JobHandle   number; 

  js  varchar2(9); -- COMPLETED or STOPPED 

  q   varchar2(1) := chr(39); 

 

 

BEGIN  /* open a new schema level import job using a default DB link */ 

   JobHandle := dbms_datapump.open ('IMPORT',

                                                                 'SCHEMA',

                                                                  network_link); 

                          

  /* restrict to the schema we want to copy */ 

  dbms_datapump.metadata_filter ( JobHandle,

                                                              'SCHEMA_LIST',

                                                               q||source_schema||q);

 

  /* remap the importing schema name to the schema we want to create */

   dbms_datapump.metadata_remap ( JobHandle,

                                                                 'REMAP_SCHEMA',

                                                                  source_schema,

                                                                  destination_schema);

  

   /* Set datapump parameters*/                            

   dbms_datapump.set_parameter ( JobHandle,

                                                             'TABLE_EXISTS_ACTION',

                                                              'REPLACE' );

                                

  /* start the job */ 

  dbms_datapump.start_job( JobHandle);   

 

  /* wait for the job to finish */ 

  dbms_datapump.wait_for_job( JobHandle, js);   

 

end;

/

4. Run the schema refresh 

                 begin        

                     DBA_TOOLS.copy_schema(‘SOURCE_SCHEMA’,'DESTINATION_SCHEMA');

                 end;

                  /

More information about  this subject ( Speed up the schema refresh over a DBlink with Data Pump (10g) ) you can get from  www.in-oracle.com