Here we have a table called requests under the SAMPLE schema, what we are going to do is export it and then re import changing its name and the tablespace with a filter.
1. Create export par file and export –
userid="/ as sysdba" directory=TTS_EXP dumpfile=requests_2015.dmp compress=y logfile=export_requests_2015.txt tables=SAMPLE.requests $ expdp parfile=exp_requests.par Export: Release 11.2.0.4.0 - Production on Tue Oct 6 12:02:43 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production With the Automatic Storage Management option Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "compress=TRUE" Location: Parameter File, ignored. Legacy Mode has set reuse_dumpfiles=true parameter. Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA parfile=exp_requests.par reuse_dumpfiles=true Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 2.222 GB >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS >>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled >>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled . . exported "SAMPLE"."requests" 1.695 GB 2800351 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /drive1/backup/export/requests_2015.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 6 12:05:55 2015 elapsed 0 00:03:08
2. Create tablespace for reimport
SQL> CREATE SMALLFILE TABLESPACE "SAMPLE_ARCHIVE_AS" DATAFILE '+DATA' SIZE 1024M REUSE AUTOEXTEND ON NEXT 250M MAXSIZE 32767M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; Tablespace created.
3. Create import par file and import
$ vi imp_requests.par userid="/ as sysdba" directory=TTS_EXP dumpfile=requests_2015.dmp logfile=import_requests_2015.txt tables=SAMPLE.requests indexes=no query=SAMPLE.requests:"where created < SYSDATE-36" remap_table=SAMPLE.requests:requests_20151006 remap_tablespace=SAMPLE_01:SAMPLE_ARCHIVE_AS $ impdp parfile=imp_requests.par Import: Release 11.2.0.4.0 - Production on Tue Oct 6 12:38:15 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production With the Automatic Storage Management option Legacy Mode Active due to the following parameters: Legacy Mode Parameter: "indexes=FALSE" Location: Parameter File, Replaced with: "exclude=index" Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA parfile=imp_requests.par Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SAMPLE"."requests_20151006" 1.695 GB 2786738 out of 2800351 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 6 12:41:17 2015 elapsed 0 00:03:01
Summary
We have exported and imported a table into a different tablespace, but at the same time renamed the tables using the REMAP_TABLE and REMAP_TABLESPACE parameters.