Re: Relocate indexes during datapump import

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Mon, 2 Mar 2015 19:53:51 +0100
Message-ID: <CA+S=qd1Y4f4H10ttrcTqOmn6kq16iDkeG4Aq_zjKUb1dxtbHdQ_at_mail.gmail.com>

Interesting difference (maybe) between command line impdp and dbms_datapump.

Take a look at TRANSFORM:

DBMS_DATAPUMP.METADATA_TRANSFORM (

   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN VARCHAR2,

   object_type IN VARCHAR2 DEFAULT NULL);

Command line version for impdp:

TRANSFORM = transform_name:value[:object_type]

The command line has the optional object_type corresponding to the optional function parameter.

Now look at REMAP:

DBMS_DATAPUMP.METADATA_REMAP (

   handle      IN NUMBER,
   name        IN VARCHAR2,
   old_value   IN VARCHAR2,
   value       IN VARCHAR2,

   object_type IN VARCHAR2 DEFAULT NULL);

The function has the same optional object_type as TRANSFORM, but that does not exist in command line:

REMAP_TABLESPACE=source_tablespace:target_tablespace

It may not be quite comparable, as function METADATA_REMAP handles the functionality of several METADATA_* parameters of impdp. But still a bit funny that the functionality of remapping tablespaces for a specific object type seems to be available in datapump, but only when using the API and not the command line?

You might just for fun try in a test environment to do REMAP_TABLESPACE=from_tablespace:to_tablespace:INDEX and see if it works even if undocumented ;-)
(Don't get your hopes high - it's a long shot...)

Alternatively consider if you can use the DBMS_DATAPUMP api? If you need it to be scriptable, couldn't you as alternative to scripting a call to impdp, instead script a call to sqlplus executing a DBMS_DATAPUMP anonymous block? That would give you the option to use all of the functionality of the API.

If that's not an option for some reason, I guess the alternative would be two impdp jobs - one that uses EXCLUDE=index without remap tablespace and then one that uses INCLUDE=index and has REMAP_TABLESPACE.

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com
_at_kibeha

On Mon, Mar 2, 2015 at 4:17 PM, John Dunn <JDunn_at_sefas.com> wrote:

> 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
> To: 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 - 19:53:51 CET