Re: Relocate indexes during datapump import
Date: Mon, 02 Mar 2015 10:28:19 -0500
Message-ID: <54F48193.8080002_at_yahoo.com>
Description of the TABLESPACES argument to impdp, from the utilities manual, version 11.2:
TABLESPACES Default: There is no default
Purpose
Specifies that you want to perform a tablespace-mode import.
Syntax and Description
TABLESPACES=tablespace_name [, ...]
Use |TABLESPACES| to specify a list of tablespacenames whose tables and dependent objects are to be imported from the source (full, schema, tablespace, or table-mode export dump file set or another database).
During the following import situations, Data Pump automatically creates the tablespaces into which the data will be imported:
*
The import is being done in |FULL| or |TRANSPORT_TABLESPACES| mode
*
The import is being done in table mode with |TRANSPORTABLE=ALWAYS|
In all other cases, the tablespaces for the selected objects must already exist on the import database. You could also use the Import |REMAP_TABLESPACE| parameter to map the tablespace name to an existing tablespace on the import database.
The use of filtering can restrict what is imported using this import mode. See "Filtering During Import Operations" <http://medo/doc/ora-11R2/server.112/e22490/dp_import.htm#i1009204>.
Restrictions
*
The length of the list of tablespace names specified for the |TABLESPACES| parameter is limited to a maximum of 4 MB, unless you are using the |NETWORK_LINK| parameter to a 10.2.0.3 or earlier database or to a read-only database. In such cases, the limit is 4 KB.
Example
The following is an example of using the |TABLESPACES| parameter. It assumes that the tablespaces already exist. You can create the |expfull.dmp| dump file used in this example by running the example provided for the Export |FULL| parameter. See "FULL" <http://medo/doc/ora-11R2/server.112/e22490/dp_export.htm#i1006790>.
> impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLESPACES=tbs_1,tbs_2,tbs_3,tbs_4
This example imports all tables that have data in tablespaces |tbs_1|, |tbs_2|, |tbs_3|, and |tbs_4|.
On 03/02/2015 10:17 AM, John Dunn 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 <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
>
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 02 2015 - 16:28:19 CET