Transportable Tablespaces With Segment Dependencies

Transportable tablespaces allow moving of tablespaces between databases. The transportable tablespace must contain all the segments within itself. If there are segment dependencies between tablespace this would require all the dependent tablespaces being moved. This post shows a steps in transporting three tablespaces which have segment dependencies between them.
1. Create three tablespaces in this case one for storing table data, one for indexes and one for lob segments.

create tablespace tabletbs datafile '+data(datafile)' size 10m;
create tablespace indextbs datafile '+data(datafile)' size 10m;
create tablespace lobstbs datafile '+data(datafile)' size 10m;

2. Grant the user quotas on the tablespaces

alter user asanga quota unlimited on tabletbs quota unlimited on indextbs quota unlimited on lobstbs;

3. Create the segments such that all tablespaces are used.

create table abc (a number, b varchar2(100)) tablespace tabletbs;

create table def (b number, c blob) tablespace tabletbs lob(c)
     STORE AS c_lob_seg (
        TABLESPACE lobstbs
        CHUNK 32K
        CACHE
        STORAGE (MAXEXTENTS UNLIMITED)
        INDEX c_lob_idx (
            TABLESPACE lobstbs
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    );  

create index aidx on abc(a) tablespace indextbs;
create index caidx on def(b)  tablespace indextbs;

4. Populate the tables

SQL> begin
      for i in 1 .. 1000
      loop
      insert into abc values(i,'abc'||i);
      end loop;
      commit;
      end;
      /

SQL> begin
      for i in 1000 .. 2001
      loop
      insert into def values(i,'def'||i);
      end loop;
      commit;
      end;
      /

5. If the full transportable tablespace set is not specified this will be mentioned transport set violation view. Below output shows the transport set violation when only the tabletbs is selected for transport.

SQL>  EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tabletbs',true);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
----------------------------------------------------------------------------------------------------------------
ORA-39905: Table ASANGA.C_LOB_SEG in tablespace lobstbs points to LOB segment ASANGA.DEF in tablespace tabletbs.

When all the depended tablespaces are selected no errors are shown

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tabletbs,indextbs,lobstbs',TRUE);
PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS;
no rows selected

6. Change the tablespace to read only mode.

SQL> ALTER TABLESPACE tabletbs READ ONLY;
Tablespace altered.

SQL> ALTER TABLESPACE indextbs READ ONLY;
Tablespace altered.

SQL> ALTER TABLESPACE lobstbs READ ONLY;
Tablespace altered.

7. Export the tablespace metadata.

expdp  system/rac11g2db directory=exec_dir transport_tablespace=y tablespaces=tabletbs,indextbs,lobstbs dumpfile=tbs.dmp logfile=tbs.log

Export: Release 11.2.0.3.0 - Production on Wed Feb 18 11:39:22 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces=tabletbs,indextbs,lobstbs"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=exec_dir tablespaces=tabletbs,indextbs,lobstbs dumpfile=tbs.dmp logfile=tbs.log reuse_dumpfiles=true
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
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:
  /home/oracle/df/tbs.dmp
******************************************************************************
Datafiles required for transportable tablespace indextbs:
  +DATA/rac11g2/datafile/indextbs.269.871989879
Datafiles required for transportable tablespace lobstbs:
  +DATA/rac11g2/datafile/lobstbs.268.871989887
Datafiles required for transportable tablespace tabletbs:
  +DATA/rac11g2/datafile/tabletbs.270.871989869
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:40:38

8. Copy the data files belonging to the tablespaces being transported to remote host. Data files in ASM could be copied to local host with asmcmd's cp command and then copied remotely or use ftp service provided with XML DB.

 asmcmd cp +DATA/rac11g2/datafile/tabletbs.270.871989869 /home/oracle/tabletbs.270.871989869
copying +DATA/rac11g2/datafile/tabletbs.270.871989869 -> /home/oracle/tabletbs.270.871989869

9. Import the tablespace metadata in the remote host. Reflect the new location of the data files copied over in the transport_datafiles parameter.

impdp system/ent11g2db directory=exec_dir dumpfile=tbs.dmp logfile=imp.log 
transport_datafiles='/home/oracle/trntbs/indextbs.269.871989879','/home/oracle/trntbs/lobstbs.268.871989887','/home/oracle/trntbs/tabletbs.270.871989869'

Import: Release 11.2.0.3.0 - Production on Wed Feb 18 10:55:10 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=exec_dir dumpfile=tbs.dmp logfile=imp.log transport_datafiles=/home/oracle/trntbs/indextbs.269.871989879,/home/oracle/trntbs/lobstbs.268.871989887,/home/oracle/trntbs/tabletbs.270.871989869
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:55:13

10. After the import the tablespaces will be plugged in but in read only mode.

SQL> SELECT tablespace_name, plugged_in, status FROM   dba_tablespaces;

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
tabletbs                       YES READ ONLY
indextbs                       YES READ ONLY
lobstbs                        YES READ ONLY

Change tablespace mode to read write.

SQL> alter tablespace lobstbs read write;
Tablespace altered.

SQL> alter tablespace indextbs read write;
Tablespace altered.

SQL> alter tablespace tabletbs read write;
Tablespace altered.

SQL> SELECT tablespace_name, plugged_in, status FROM   dba_tablespaces;

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
tabletbs                       YES ONLINE
indextbs                       YES ONLINE
lobstbs                        YES ONLINE

11. The transported tablespaces are not ready for use.