Author: Bill Ennis

Introduction

Oracle 8i has introduced a new feature in data movement dubbed transportable tables. The functionality is accessed through the import and export utilities. This feature can be a great aid to those in need to move large volumes of processed data throughout a network of Oracle databases.

Terminology

An Oracle tablespace is a logical collection of physical database files that reside on the operating system.

Import and export are Oracle utilities used to extract and load data to and from Oracle databases.

Source is referred to as the system where the original copy of the data exists.

Target is referred to as the system that data is being transfereed to.

The Problem Transportable Tablespaces Solve

In order to appreciate transportable tablespaces you must have lived through the problems that it solves. Therefore, I will describe a process that I was performing repeatedly to move data through Development, Test, Staging, and Production environments for a system that I was working on.

The “prepared” data was a product catalog and had a substantial size of 5 GB. In the beginning there was just one server (Development). But as the project approached the launch date new environments needed to be constructed and loaded with the catalog data. The method used to create the catalog in the new environment was an Oracle export from the source and an import into the target. The catalog objects were owned by a catalog user and a owner level export was being performed. Exporting the data wasn’t too bad (45-60 minutes), but imports were another story (6-12 hours). And those numbers didn’t take into account the file transfer time between servers (sometimes 5 hours – I was unlucky enough to have these servers sitting in different cities connected via a VPN).

Here is the math for the tasks involved in the operation:

That can make for a long day! What if I told you you could shave about 8 hours off of that time? Interested? Ok, now you can appreciate this feature!

Explaining the feature

How can we save 9 hours? Well, if you think about it, the files belonging a tablespace already contain all the data, indexes, etc that we need. So why bother the target Oracle instance with the task of recreating all the entries for us. Transportable tablespaces allow us to export the meta-data about the tablespace ONLY. The row level information is not required! That information is already contained in the Oracle datafiles. So our export is reduced down to a meta-data export, and we literally copy the datafiles over to the target in their entirety.

Things you need to know (Caveats)

• You must put the source tablespace into read only mode as you export and copy the datafiles. This way we get an accurate and unchanging snapshot as it exists in the source environment.
• You also need to be sure that the set of tablespaces being transported are self containing. This means that none of the contents of the transport should refer to objects that will not be included in the transport. Oracle has provided a package to perform a check on this for you.
• Some objects do not get transported. I noticed that sequences, for example needed to be manually recreated after the transport was complete. This is because Oracle sequences actually exist in the SYSTEM tablespace. I have not yet found a way around this.
• The tablespace names that are being trtansported must not already exist in the target database.
• You do have the ability to place the datafiles in a location that is different from the source database. One possible trick you could use here would be to create links where filesystems differ from source to target.
• The export and import must be run as the sys user. We are creating tablespaces here so the normal security observed by Oracle applies.

Process

The overall process can be broken down at a high level into the following steps:

1. Identify the tablespace(s) to be transported.

   The objects being transported (including tables, indexes, etc) must be contrained to the tablespace set and not include references to objects that are not incuded in the tablespace set. For example, if you are transferring a tablespace named TABLE_TBS that includes a table named stores that has an index that is in the INDEX_TBS then you must transfer both tablespaces.

2. Verify that these tablespaces can be transported using Oracle supplied packages.

   Oracle supplies a package to assist on this verification that is named DBMS_TTS.

3. Put the tablespaces in the source database into read only mode.

   This step assures consistency of the underlying datafiles that will be transferred to the target database.

4. Run a tablespace level export on the designated set of tablespaces.

   This is just the meta data information and not the actual data. Therefore, this operation will be very fast.

5. Copy the datafiles to an alternate location on the source database machine.

   I like to separate this step from the actual transfer so that I can bring the tablespaces out of read only mode as soon as possible. Transferring can take a long time for large datafiles.

6. Take the tablespaces out of read only mode in the source database.
7. Transfer the datafiles to the target

It is a good idea to compress large datafiles first to speed up the file transfer. It is important to note that the datafiles do NOT have to be placed in the same location as they existed on the source. The tablespace import allows an option to designate the location of the datafiles at the target database.

8. Verify that the users that own the objects to be imported exist in the target database and that they have the appropriate privileges to create the objects contained in the transported tablespace set.
9. Run a tablespace level import on the target database.
10. Take the tablespaces out of read only mode in the target. Remember that when they were exported they were in backup mode!
11. Create any objects that were not included in the transport.

I have found that sequences are not transferred during a tablespace transport. This is because Oracle actually stores sequence information in the system tablespace and you should NOT transport a system tablespace!

The specific syntax required to perform this exercise can be found in the Oracle reference manuals. In particular, refer to syntax for the following:

• ALTER TABLESPACE command
• DBMS_TTS package
• Exp utility
• Imp utility

Summary

Transportable tablespaces can be an excellent tool for migrating data that has been transformed and loaded into a database. It is much faster than export/import because it is basically a meta data copy within Oracle along with a straight file copy to the destination system. Transporatable tablespaces should be in any data integration toolbox.