TRANSPORT TABLESPACE MADE SAFE AND EASY...

Migrating data to and fro in a database(or between database) is quite a tedious task when it comes to data loss and other critical issues for the organisation.

But it can be very handy using few commands of DATAPUMP.

Here is the show for you...

We have a tablespace "maptest" which will be tranported.Here is the data contained in this
in a table called "tra"

SQL> select * from ret.tra;

        ID
----------
        10
        20
        30
        40
        10
        20
        30
        40
        10
        20
        30

11  rows selected.

----Make the tablespace read only-----------

SQL> alter tablespace maptest read only;

Tablespace altered.

-----------META DATA of tablespace is exported------------------

SQL> host expdp system/rman dumpfile=trans.dmp logfile=trans.log transport_table
spaces=maptest transport_full_check=y

Export: Release 11.1.0.6.0 - Production on Thursday, 06 January, 2011 16:33:24

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=trans
.dmp logfile=trans.log transport_tablespaces=maptest transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  D:\ORACLE\ADMIN\RMAN\DPDUMP\TRANS.DMP
******************************************************************************
Datafiles required for transportable tablespace MAPTEST:
  C:\MAPTEST01.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:35:03

---------------Now connect to target database-----------------

C:\>sqlplus sys/sys@orcl as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jan 6 16:37:12 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME
---------
ORCL

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE

6 rows selected.

----------Copy the required datafile in target database location using OS commands.--------

c:\>copy   <source> to <destination>


SQL> host impdp system/rman dumpfile= trans.dmp transport_full_check=y transport_d
atafiles='d:\oracle\oradata\orcl\maptest01.dbf'


Lets verify the data again...


SQL> select * from ret.tra;

        ID
----------
        10
        20
        30
        40
        10
        20
        30
        40
        10
        20
        30


11 rows selected.



:-) Its safe and correct as you see...