Oracle expdp tablespace mode

In my first expdp article, I have explained how to use expdp as sysdba and also other options such as nohup, excluding schemas, compression etc.  In this article, I will give you an example of expdp in tablespace mode. Traditionally, when one wants to take a schema backup, one uses expdp always as it is a logical backup.  For tablespace backups, most of the times DBAs take physical backups of tablespaces instead of using expdp.

However expdp too provides the tablespace option.  Infact, several tablespaces can be backed up at once using expdp. Also one can combine other options such as parallel,compression etc.  The below command is one such example.

expdp TABLESPACES=FINTS_DATA,HRTS_DATA dumpfile=DATA_PUMP_DIR:ts_dumps_%U.dmp LOGFILE=DATA_PUMP_DIR:ts_dumps_log.log    parallel=2    filesize=20G   compression=ALL

In the above example, expdp takes a backup of the contents of FINTS_DATA and HRTS_DATA. The expdp also runs quickly because of the parallel=2 option (provided there are more CPUs available in the database server). 

Furthermore, the filesize option above specifies that each dumpfile generated should not be more than 20G in size.  Oracle will create a second dumpfile if the first dumpfile's size already reaches the 20G threshold.  The %U in the dumpfile name guarantees uniqueness in the name.  

The compression option is a new feature in Oracle 11g. It compresses the output dumpfile by almost a ratio of 1:10 approximately. This is a great feature if the tablespaces have bigger sizes and one wants to save space in the DATA_PUMP_DIR directory for their exported dumpfiles.  Also, during a subsequent impdp process, Oracle automatically decompresses these dumpfiles during the data load, requiring no additional impdp option.