Transporatable Tablesapces

For the last 10 days, I have spent a great amount of time doing R&D on TTS, reading Metalink articles, whitepapers and oracle docs. I have compiled some notes extracting information from all these docs so that I can use it as reference.


Transportable Tablespaces
The transportable tablespaces feature is introduced in V8.1.5 the first release of racle8i; this feature is sometimes referred to as pluggable tablespaces. You need to have installed the Enterprise Edition of Oracle to generate a transportable tablespace set, but any edition of Oracle (Enterprise, Standard or Personal) can be used to plug a transportable tablespace set into an Oracle database. The tablespaces being transported can be either dictionary managed or locally managed

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

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 on another database:
- Restore the files in the new location
- Import the metadata of the tablespace specifying the new location of the files


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 o the dictionary for internal use (STRADDLING_TS_OBJECTS,
TS_PLUG_INFO, UNI_PLUGGABLE_SET_CHECK, 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.

RESTRICTIONS
Principal limitations for transportable tablespaces are:

In Oracle8i and Oracle9i, the source and target database had to be on the same hardware platform. For example, you could transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between NT Oracle databases. However, you couldn't transport a tablespace from a SUN Solaris Oracle database to an NT Oracle database. Starting with Oracle Database 10g, you can transport tablespaces across platforms. If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

Character set as well as national character set of the source and the target must be the same

In Oracle8i one of the restrictions was that the block size of both databases must be the same. In Oracle9i the introduction of multiple block sizes has removed this restriction

Users whose default tablespace is getting exported must exist in the target database before importing.
Target database must not have tablespace of the same name. (If you are using 10g or above, you'll be able to rename existing tablespace.)

The tablespace must be self-contained (no reference pointing outside this tablespace like index, constraint, partition, LOB...). This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check
Example:
EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list=>'POOL_DATA', incl_constraints => TRUE);

The TRANSPORT_SET_VIOLATIONS view can be used to check for any violations:
SELECT * FROM sys.transport_set_violations;

Oracle versions in source and target must be 8.1.x and above.

You cannot transport the SYSTEM tablespace, or SYSTEM tablespace objects or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.

Most database entities, such as data in a tablespace or structural information associated with the tablespace, behave normally after being transported to a different database. Some exceptions arise with the following entities:
- Advanced queues: transportable tablespaces with 8.0-compatible advanced queues with multiple recipients are not supported.
- Domain indexes: only supported with Oracle9i and Oracle10g transportable tablespaces.
- Materialized views/snapshot/replication: only supported with Oracle10g transportable tablespaces.
- Function-based indexes: only supported with Oracle10g transportable tablespaces (PL/SQL function-based indexes are not supported).
- Scoped REFs: only supported with Oracle10g transportable tablespaces.

Transportable Tablespaces FAQs:

Q: How to Connect AS SYSDBA when Using Export or Import?
This document demonstrates how to connect AS SYSDBA when starting an export or import. Incorrect usage of single or double quotes can result in errors.

Windows: exp 'sys/change_on_install@instance as sysdba' tables=scott.emp
Unix : exp \'sys/change_on_install@instance as sysdba\' tables=scott.emp

Q: Can a tablespace be renamed when transporting to the target database?
This will be possible with the Oracle10g release. With Oracle9i and lower releases this is not possible.
Transported tablespaces are plugged in a read-only configuration; however the file header also stores the tablespace name, so any attempt to rename it would require an update to the file header. Similarly at unplug time there is a requirement that the tablespace is in read only mode and the same restrictions therefore apply - you can't
'RENAME' a tablespace, because you might only be taking a copy of the datafile.

Q: Can a datafile be renamed when transporting to the target database?
Yes. For this you need to use the OpenVMS rename command. Change the physical file name as desired after copying to the target directory. Then use the new datafile name in the DATAFILES parameter of the import utility.
Example:

1. Assume the data file TEST_2.dbs has been copied from the source database to the target database under the original physical name.
2. Use the OpenVMS RENAME command to alter the physical file name at the OS level.
Example: $ rename TEST_2.dbs TEST_9.dbs
3. Edit the import parameter file and change TEST_2.dbs to TEST_9.dbs:
USERID='SYS/CHANGE_ON_INSTALL AS SYSDBA'
TRANSPORT_TABLESPACE=Y
TABLESPACES=SCOTT_TS
DATAFILES=(TEST_9.DBS,TEST_1.DBS)
FILE=SCOTT_TS.DMP
4. Import the metadata.
5. Review V$DATAFILE to see that TEST_9.dbs is there and TEST_2.dbs is not.
Q: What order will the datafiles be added on the target database?The order is controlled automatically by the export and import operations. Changing the order of the physical file names within the DATAFILES parameter of the import utility has no affect on the order that files are added to the target database. With the import operation only the absolute file numbers will change; the relative file numbers remain unaltered.
Q: What schema will own the tables in the transported tablespace?
The tables will be owned by the same schema owner as in the source database unless the FROMUSER/TOUSER import parameters are used.

Q: Will the schema owner be created automatically in the target database?
No. You must have the schema owner (database user) already set up in the target database.

Q: Are ROWID's modified during the import on the target database?
No. ROWID's are dependent upon relative file numbers. These file numbers remain the same when importing the tablespaces into the new database. Only the absolute file numbers are changed. See the Oracle Administrator's Guide for more information.

Please review below MetaLink articles for more information.
Note 77523.1 - Transportable Tablespaces -- An Example to setup and use
Note 100693.1 - Getting Started with Transportable Tablespaces
Note 103013.1 - Renaming Tablespace Using Transportable Tablespace Feature
Note 132904.1 - Compatibility Matrix for Export & Import between Different Oracle Versions
Note:144212.1 - Transporting Tablespaces with Different Block Sizes Between Databases

Example: http://www.oracle-base.com/articles/9i/TransportableTablespaces9i.php