RE: Relocate indexes during datapump import

From: John Dunn <JDunn_at_sefas.com>
Date: Mon, 2 Mar 2015 15:17:11 +0000
Message-ID: <EEC3316514CFB24AB2285394E0D405587C8747B8_at_BOU.sefasuk.local>

Thanks for the quick response.
Can I do the same using the command line impdp command?

I am struggling to find an example of that.

John

From: Alexey B. Danchenkov [mailto:dabron_at_hotmail.com] Sent: 02 March 2015 14:17
To: John Dunn; oracle-l_at_freelists.org Subject: RE: Relocate indexes during datapump import

Hi John,

Yes, it is possible to relocate indexes separately to a different tablespace using Data Pump. For example, you can use a PL/SQL code which uses Data Pump API. This sample code moves only indexes from OLDTABLESPACE' to NEWTABLESPACE:

DECLARE
  hd NUMBER;
BEGIN
  hd := DBMS_DATAPUMP.OPEN(operation => 'IMPORT',job_mode => 'FULL',remote_link => NULL,job_name => 'DDD',version => 'COMPATIBLE' );

  DBMS_DATAPUMP.ADD_FILE( handle => hd, filename => 'dumpfile.dmp', directory => 'EXPDIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE  );
  DBMS_DATAPUMP.ADD_FILE( handle => hd, filename => 'logfile.log', directory => 'EXPDIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE  );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'REUSE_DATAFILES', value => 0 );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'SKIP_UNUSABLE_INDEXES', value => 0 );
  DBMS_DATAPUMP.METADATA_REMAP ( handle => hd, name => 'REMAP_TABLESPACE', old_value => 'OLDTABLESPACE', value => 'NEWTABLESPACE', object_type => 'INDEX' );
  DBMS_DATAPUMP.SET_PARAMETER( handle => hd, name => 'TABLE_EXISTS_ACTION', value => 'SKIP' );
  DBMS_DATAPUMP.START_JOB(handle => hd);

END;
/

cheers,
Alexey Danchenkov

---

From: JDunn_at_sefas.com<mailto:JDunn_at_sefas.com> To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Relocate indexes during datapump import Date: Mon, 2 Mar 2015 14:04:38 +0000
During a datapump import is it possible to relocate indexes to a separate tablespace?

Or I do I need to do that before doing the original export?

John

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

Received on Mon Mar 02 2015 - 16:17:11 CET