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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s