Table Space Management   «Prev  Next»

Lesson 4 Transportable tablespaces
Objective Define transportable tablespaces.

Define Transportable Tablespaces

When copying large chunks of data from one database to another prior to Oracle, you had to choose among several options, all of which were time-consuming and required you to create an intermediate file to hold the data. Oracle has introduced a new feature for copying or moving a set of tablespaces from one Oracle database to another, called the transportable tablespace. The transportable tablespace feature makes it as easy to move database data as it is to move a file from one location to another. Some of the uses for this feature are:
  1. Preparing data in one database and then copying the data to several distributed databases
  2. Gathering data into summarized historical tables, then moving the tablespace to your data warehouse
Question: Does the current release of Oracle support the transporting and cloning of tablespaces?
Yes, the current release of Oracle supports the transporting and cloning of tablespaces. There are two main methods for transporting and cloning tablespaces:
  1. Transportable Tablespaces (TTS)
  2. Data Pump
TTS is a feature of Oracle that allows you to move a tablespace from one database to another without having to take the database offline. TTS uses a set of metadata files and datafiles to create a transportable tablespace set. The transportable tablespace set can then be moved to the destination database and imported. Data Pump is a feature of Oracle that allows you to export and import data from one database to another. Data Pump can be used to transport and clone tablespaces by exporting the tablespace datafiles and metadata and then importing them into the destination database.
Both TTS and Data Pump are supported in the current release of Oracle. The choice of which method to use depends on your specific needs. If you need to move a tablespace quickly and easily, then TTS is a good option. If you need to move a tablespace that contains a large amount of data, then Data Pump is a better option.
Here are some of the benefits of transporting and cloning tablespaces:
  1. You can move a tablespace from one database to another without having to take the database offline.
  2. You can clone a tablespace to create a new, identical tablespace.
  3. You can use transporting and cloning to migrate data between different platforms.
The current release of Oracle restricts the transporting and cloning of tablespaces. You can only do this between computers with the same hardware, database block size, and database character set. This restriction will likely disappear in later releases as well.
Objects in the tablespaces that you wish to transport must not reference any object outside the tablespaces. The tablespaces must be totally self-contained. For example, let us say you select a tablespace that contains an index. The index is based on a table found in another tablespace. The initial tablespace cannot be transported by itself because it refers to something outside of itself. You could transport the two tablespaces as a set, however, assuming that the two only reference each other. Look at the following series of images below to see a demonstration of the steps for transporting a tablespace.

Oracle Transportable Tablespaces

1)First step in the process is to change the tablespace you wish to transport into READ ONLY status
1) First step in the process is to change the tablespace you wish to transport into READ ONLY status. This example shows the command to change the WAREHOUSE1 tablespace so that it is in READ ONLY status. If you are exporting a set of tablespaces, repeat this command for each one.

2) Second step in the process is to export the tablespace. This screen shows the syntax of the command you will use and the actual export command used in our example
2) 
EXP PARFILE ='parameterfile'
Parameter file contents
Second step in the process is to export the tablespace. This screen shows the syntax of the command you will use and the actual export command used in our example. You create a parameter file that contains all the details needed to export the file. The green highlighted area show optional parameters that can save exporting time if used with the 'n' option. For example, if you know that there are no grants you wish to export, specify GRANTS =n and the export process will skip this portion of its routine.


3) Screen shot of the command line and status feedback that was created when exporting the example tablespace.
3) Screen shot of the command line and status feedback that was created when exporting the example tablespace.

4) The next step is to copy the export file just created and all the data files for the tablespaces to a location that is accessible by the target database.
4) The next step is to copy the export file just created and all the data files for the tablespaces to a location that is accessible by the target database. This screen shows you how you might accomplish this in the Windows environment using Windows Explorer, but you use any operating system tool that you prefer for copying and pasting the files.

5) Now use the import utility (IMP) to import the meta-data into the target database. This screen shows the syntax and the exact command for our sample tablespace, WAREHOUSE1.
5) Now use the import utility (IMP) to import the meta-data into the target database. This screen shows the syntax and the exact command for our sample tablespace, WAREHOUSE1. The details are again placed into a parameter file that is named in the IMP command. The green highlight shows optional parameters for listing all the owners in the tablespace (TTSOWNERS) and listing how to substitute one owner for another (FROM USER and TOUSER) when doing the import process. When these are left out, the owners of all the object remain unchanged.

6) Here is a screen shot of the command line and status feedback that was created when importing the example tablespace
6) Here is a screen shot of the command line and status feedback that was created when importing the example tablespace.

7) You can now modify the tablespace so that it is in a READ WRITE state using the command show here.
7) You can now modify the tablespace so that it is in a READ WRITE state using the command show here.

  1. First step in the process is to change the tablespace you wish to transport into READ ONLY status.
  2. Second step in the process is to export the tablespace. This screen shows the syntax of the command you will use and the actual export command used in our example.
  3. Screen shot of the command line and status feedback that was created when exporting the example tablespace.
  4. The next step is to copy the export file just created and all the data files for the tablespaces to a location that is accessible by the target database
  5. Now use the import utility (IMP) to import the meta-data into the target database. This screen shows the syntax and the exact command for our sample tablespace, WAREHOUSE1.
  6. Here is a screen shot of the command line and status feedback that was created when importing the example tablespace.
  7. You can now modify the tablespace so that it is in a READ WRITE state using the command show here.

The transporting of tablespaces as if they were ordinary files (except for the meta-data, which must be imported) reduces the time it takes to copy or move data from one database to another. In fact, the greater the size of the data files, the more time is saved compared to a "normal" export and import of the data.

Transportable TableSpaces - Exercise

Click the Exercise link below to practice creating a locally managed tablespace and exporting it for transport.
Transportable TableSpaces - Exercise
The next lesson looks into new features and enhancements you will use when handling READ ONLY tablespaces.