EXPORT AND IMPORT TABLESPACES USING DATAPUMP ORACLE
To export a whole tablespace from a database, you can use datapump with parameter ‘tablespaces‘ . This can be used in situation where you need all the user objects residing in particular tablespace rather than specific objects of user
[oracle@orcl ~]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tbspcusr.dmp logfile=tbspc.log tablespaces=users
Export: Release 12.1.0.2.0 - Production on Mon Jul 19 16:49:20 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLESPACE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tbspcusr.dmp logfile=tbspc.log tablespaces=users
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5.25 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "PUJA"."T" 3.998 MB 71000 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "TEST"."T1" 5.523 KB 1 rows
. . exported "HR"."CONTACTS" 0 KB 0 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "TEST"."T" 0 KB 0 rows
. . exported "TEST"."T3" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLESPACE_01 is:
/oracle/base/admin/orcl/dpdump/tbspcusr.dmp
Job "SYS"."SYS_EXPORT_TABLESPACE_01" successfully completed at Mon Jul 19 22:20:15 2021 elapsed 0 00:00:49
To import the tablespace to destination database use ‘remap_tablespace‘
[oracle@orcl ~]$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=tbspcusr.dmp logfile=tbspc.log remap_tablespace=users:users1 table_exists_action=replace
Import: Release 12.1.0.2.0 - Production on Mon Jul 19 16:58:01 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=tbspcusr.dmp logfile=tbspc.log remap_tablespace=users:users table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PUJA"."T" 3.998 MB 71000 rows
. . imported "SCOTT"."DEPT" 6.023 KB 4 rows
. . imported "SCOTT"."EMP" 8.773 KB 14 rows
. . imported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . imported "TEST"."T1" 5.523 KB 1 rows
. . imported "HR"."CONTACTS" 0 KB 0 rows
. . imported "SCOTT"."BONUS" 0 KB 0 rows
. . imported "TEST"."T" 0 KB 0 rows
. . imported "TEST"."T3" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 19 22:28:42 2021 elapsed 0 00:00:39