BASICS OF TRANSPORTABLE TABLESPACES
===================================
This feature is sometimes referred to as pluggable tablespaces
The transportable tablespace mechanism consists in the combination of 2 things: - An export of the metadata ( instead of the data) of a tablespace - A copy of the files belonging to the tablespace
These 2 parts can then be transferred onto another compatible operating system in order to "plug" the tablespace with its files onanother database: - Restore the files in the new location - Import the metadata of the tablespace specifying the new location of the files
Therefore, the whole operation is much faster than a classical EXPORT/IMPORT or a unload/load of data.
The appropriate environment is set by SYS when
$ORACLE_HOME/rdbms/admin/catproc.sql is executed.
More specifically, catproc script runs the following scripts to implement the transportable tablespaces: - $ORACLE_HOME/rdbms/admin/catplug.sql - $ORACLE_HOME/rdbms/admin/dbmsplts.sql - $ORACLE_HOME/rdbms/admin/prvtplts.plb The SYS packages DBMS_PLUGTS and DBMS_TTS are available to users to handle transportable tablespaces. Some views have been modified to reflect this new feature. In particular,
the column PLUGGED_IN has been added to views V$DATAFILE (value 1 or 0) and DBA_TABLESPACES (value YES or NO).
In addition, some views have been added to the dictionary for internal use
1) STRADDLING_TS_OBJECTS
2) TS_PLUG_INFO
3)UNI_PLUGGABLE_SET_CHECK
4)PLUGGABLE_SET_CHECK EXPORT command has new keywords: TRANSPORT_TABLESPACE and TABLESPACES . IMPORT command has also new keywords: TRANSPORT_TABLESPACE , TABLESPACES , DATAFILES and TTS_OWNERS.
PROCESS OF TRANSPORTABLE TABLESPACES
====================================
Step 1: Create the required environment ----------------------------------------
Make the tablespace read-only in the source system
SQL> alter tablespace pj_ts read only; Tablespace altered.
Step 2 : Export the transportable tablespace: ---------------------------------------------
exp userid=\"sys/<password> as sysdba\" file=pjexp.dmp \ transport_tablespace=y tablespaces=pj_ts
Step 3: Transfer the datafiles and the export file --------------------------------------------------
By ftp/scp copy all the datafiles of pj_ts tablespace to target server
By ftp/scp copy the metadata dump file pjexp.dmp to target server
Step 4 : Plug the new datafile in the target database -----------------------------------------------------
imp sys/<password> file=pjexp.dmp transport_tablespace=y \ datafiles=/ora81/ora816/pjacob/pj_target.dbf
RESTRICTIONS OF TRANSPORTABLE TABLESPACES
=========================================
1. Operating system and processor type must be the same at source and target database (restriction for 8i and 9i target databases; allowed in 10g and higher target databases)
2. Character set as well as national character set of the source and the target must be the same.
3. Users whose default tablespace is getting exported must exist in the target database before importing.
4. Target database must not have tablespace of the same name.
5. The tablespace must be self contained to ensure that the table is complete in case of partitioning.
LIMITATIONS OF TRANSPORTABLE TABLESPACES
========================================