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. ExampleThe 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@xxxxxxxxxxx] *Sent:* 02 March 2015 14:17 *To:* John Dunn; 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; BEGINhd := 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 +0000During 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
