Once this recovery is complete, the next most important part is to import the EXAMPLE tablespace into the current database via the transfer tablespace technique.
Consider this scenario, when doing a cross-database migration, where data from one user tablespace needs to be migrated to another database, what method should be used?
The most conventional approach might be to export all the data via the EXP tool and then IMP import it on the target database, but this method can be slow. The EXP tool also offers another technique - transportable tablespace technology - that can be used to speed up this process.
In the help for exp -help, one can see a parameter such as
TRANSPORT_TABLESPACE Exporting transportable tablespace metadata (N)
With this option, we can export only metadata for a set of self-contained, read-only tablespaces, then copy the data files of these tablespaces to the target platform at the OS level and import the metadata into the data dictionary (a process called inserting, plugging), i.e., the migration is complete.
Transfer tablespace technology cannot be applied to the SYSTEM tablespace.
There is an important concept for transportable tablespaces.
In a tablespace transfer, the tablespace set is required to be self-contained; self-contained means that the internal tablespace set used for the transfer has no references to the external tablespace set. There are two types of self-containment: general self-contained tablespace sets and fully (strictly) self-contained tablespace sets.
The following are common violations of the principle of self-containment.
The index is in the internal tablespace set and the table is in the external tablespace set (conversely, the self-containment principle is not violated if the table is in the internal tablespace set and the index is in the external tablespace set). Partitioned tables are partly in the internal tablespace set and partly in the external tablespace set (for partitioned tables, either all of them are included in the internal tablespace set or none of them are). If a constraint is transferred along with the tablespace, the self-contained constraint is violated for referential integrity constraints where the table pointed to by the constraint is in an external tablespace set; if the constraint is not transferred, it is irrelevant to the constraint pointing. A table in the internal tablespace set and lob columns in the external tablespace set violates the self-contained constraint.
It is usually possible to check whether a tablespace is self-contained via the system package DBMS_TTS, and validation can be performed in two ways: non-strictly and strictly.
The following is a simple validation process that assumes the existence of a table eygle in the eygle tablespace on which indexes exist stored in the USERS tablespace:,.
SQL> create table eygle as select rownum id ,username from dba_users;
SQL> create index ind_id on eygle(id) tablespace users;
To perform a non-strict self-contained check (full_check=false) as SYS user.
Perform strict self-containment check (full_check=true).
In turn for the USERS tablespace, non-strict checks will not pass.
However, some of the self-contained problems can be solved by simultaneous transfers to multiple tablespaces.
Once the tablespace self-inclusion is confirmed, it is easy to perform a tablespace transfer, which generally consists of the following steps.
1.To set the tablespace to read-only.
alter tablespace users read only;
2.Exporting tablespaces. At the operating system prompt, execute.
exp username/passwd tablespaces=users transport_tablespace=y file=exp_users.dmp
The export file here contains only metadata, so the export file is small and the export will be fast.
Transfer the exported metadata file (in this case exp_users.dmp) and the data file of the transfer tablespace (in this case the data file user01.dbf of the users tablespace) to the target host (care should be taken to use the binary method if the transfer process uses the FTP method).
Insert the tablespace into the database at the target database to complete the tablespace transfer. Execute the following statement at the operating system command prompt.
imp username/passwd tablespaces=users transport_tablespace=y file=exp_users.dmp datafiles='users01.dbf'
After understanding Oracle's transportable tablespace technology, take a look at the EXAMPLE tablespace insert, the following script is still from the mkplug.sql script.