Friday, June 11, 2010

Tablespace export/import

Following command is not Transportable Tablespace but datapump export/import for tablespace.
Below steps can be performed, if it is required to drop and recreate a tablespace on the same database.

To import on different instance, you'll require to create the tablespace before importing

Tablespace EXPORT/IMPORT:

1. Datapump Export Tablespace
 
expdp system/**** full=n tablespaces=SCOTT_DATA directory=dump 
dumpfile=scott_data.dmp logfile=scott.log 
 
2. Drop tablespace

DROP TABLESPACE SCOTT_DATA INCLUDING contents 
And datafiles cascade constraints;

3. Create tablespace 

4. Datapump Import Tablespace

impdp system/**** directory=dump dumpfile=scott_data.dmp full=y 
logfile=scott_imp.log