Expdp / Impdp Tablespace Parameter Benefits and Examples

Oracle data pump provides tablespace parameter to export and import the data in tablespace level. You can club one or more tablespaces in single dump while export and you can import one or more tablespace from the full database dump during the import. The benefits of this option are as below.
a.   It would be very helpful during the tablespace level database reorganizations. The backup can be performed only on the targeted tablespace.

b.   This option can be used for recreating the tablespace with different block sizes for a particular tablespace. Backup the tablespace using tablespace option -> drop the tablespace -> recreate the tablespace with different block size -> import the data.
c.   Tablespace option is helpful for the data porting between different environments of the applications which are concentrated only on few tablespace. We can export only those relevant tablespace data and can be imported into TEST or DEV environments by using the dumps from source environment. 
Example
$ expdp job_name=tabspace tablespaces=TRNLOG,TRNSR dumpfile=exp_tabspace.dmp logfile=exp_tabspace.log directory=EXP_DIR

Export: Release 11.2.0.3.0 - Production on Wed Sep 25 07:13:42 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "SYS"."TABSPACE":  /******** AS SYSDBA job_name=tabspace tablespaces=TRNLOG,TRNSR dumpfile=exp_tabspace.dmp logfile=exp_tabspace.log directory=EXP_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 74 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
. . exported "TRNLOG"."AUDITLOG"                         48.55 MB  231147 rows
. . exported "TRNLOG"."MESSAGE"                          99.54 KB     142 rows
. . exported "TRNLOG"."OPENJPA_SEQUENCE_TABLE"           5.437 KB       1 rows
. . exported "TRNSR"."ATTRIBUTE"                         6.578 KB      12 rows
. . exported "TRNSR"."CONTACT"                           7.375 KB      52 rows
. . . . .
. . . . .
. . . . .
. . . . .
Master table "SYS"."TABSPACE" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.TABSPACE is:
  /u07/exp_dir/exp_tabspace.dmp

Job "SYS"."TABSPACE" successfully completed at 07:14:32
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google