Using transportable tablespaces

• Limitations
• same character set and national character set
• tablesapace must not exists in taget database
• unscope : tablespace system, SYS objects

• Steps

            Step 1:  Determine if Platforms are Supported and Endianness

                    select d.platform_name, endian_format  from v$transportable_platform tp, v$database d     where tp.platform_name = d.platform_name;

                    If # convert tablespace ( cf RMAN page for conversion ) 

            Step 2: Pick a Self-Contained Set of Tablespaces

                execute dbms_tts.transport_set_check('sales_1,sales_2', true); execute_catalog_role is required

                select * from transport_set_violations; 

            Step 3: Generate a Transportable Tablespace Set

                ALTER TABLESPACE sales_1 READ ONLY;

                 ALTER TABLESPACE sales_2 READ ONLY;

                 EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir                     TRANSPORT_TABLESPACES = sales_1,sales_2

 or

                 EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir   TRANSPORT_TABLESPACES=sales_1,sales_2 TRANSPORT_FULL_CHECK=Y # fails if outside depends exist

                 Step 4: Transport the Tablespace Set

                     host copy datafiles and dump files

                 Step 5: Plug In the Tablespace Set

                     IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir   TRANSPORT_DATAFILES= /salesdb/sales_101.dbf, /salesdb/sales_201.dbf REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)

                     ALTER TABLESPACE sales_1 READ WRITE;

                     ALTER TABLESPACE sales_2 READ WRITE;