Re: Relocate indexes during datapump import
- From: Mayen Shah <mshah@xxxxxxxxxxxxxxx>
- To: "<JDunn@xxxxxxxxx>" <JDunn@xxxxxxxxx>
- Date: Tue, 3 Mar 2015 12:22:46 +0000
Haven't tried this but may be worth testing. You can do import with
exclude=index and then second import only for indexes with remap tablespace.
- Mayen
On Mar 3, 2015, at 4:03 AM, John Dunn <JDunn@xxxxxxxxx<mailto:JDunn@xxxxxxxxx>>
wrote:
Thanks for all your suggestions.
I think what I will do is use alter index to move the indexes after the import.
Seems simpler!
Thanks again
John
From: Alexey B. Danchenkov [mailto:dabron@xxxxxxxxxxx]
Sent: 02 March 2015 19:05
To: Kim Berg Hansen; John Dunn
Cc: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: Relocate indexes during datapump import
Actually, that is the reason why I have advised Jogn about using Data Pump API
for this purpose ;)
________________________________
Date: Mon, 2 Mar 2015 19:53:51 +0100
Subject: Re: Relocate indexes during datapump import
From: kibeha@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
To: JDunn@xxxxxxxxx<mailto:JDunn@xxxxxxxxx>
CC: dabron@xxxxxxxxxxx<mailto:dabron@xxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:kibeha@xxxxxxxxx>
@kibeha
On Mon, Mar 2, 2015 at 4:17 PM, John Dunn
<JDunn@xxxxxxxxx<mailto:JDunn@xxxxxxxxx>> 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@xxxxxxxxxxx<mailto:dabron@xxxxxxxxxxx>]
Sent: 02 March 2015 14:17
To: John Dunn; oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx<mailto:JDunn@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>
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
Other related posts:
