Export oracle tablespace with Data Pump

We can also export tablespace[s] with the help of TABLESPACES parameter in data pump utility.

In this, we can backup only the tables contains in specified set of tablespaces. Table will be backup with the dependent objects.
Note: In this mode both object metadata and data is backup.

Syntax:
TABLESPACES = tablespace_1, tablespace_2, [,..]

 

Consider following hands-on exporting user tablespace from orcl database:

[oracle@oracle ~]$ expdp system/manager directory=data_pump_bkup dumpfile=user_tbs.dmp logfile=user_tbs.log tablespaces=users
Export: Release 11.2.0.1.0 – Production on Wed Feb 12 11:05:38 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64-bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYSTEM”.”SYS_EXPORT_TABLESPACE_01″: system/******** directory=data_pump_bkup dumpfile=user_tbs.dmp logfile=user_tbs.log tablespaces=users

. . exported “SCOTT”.”DEPT” 5.937 KB 4 rows
. . exported “SCOTT”.”EMP” 8.570 KB 14 rows
. . exported “SCOTT”.”SALGRADE” 5.867 KB 5 rows
. . exported “TEST”.”EMPLOYEE” 8.578 KB 14 rows
. . exported “SCOTT”.”BONUS” 0 KB 0 rows
. . exported “TEST”.”DEPT” 0 KB 0 rows
. . exported “TEST”.”EMP” 0 KB 0 rows

Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is: /home/oracle/app/data_pump_bkup/user_tbs.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLESPACE_01″ successfully completed at 11:05:57

Above expdp command will export mentioned tablespace along with its dependent objects.

************************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.

Leave a Reply