• Using Oracle's Export and Import utilities -- This method will extract the tables and its dependent objects from the source database (using the EXP utility) and create an Oracle proprietary file that can be imported into the target database (using the IMP utility). This method can be scripted and is conceptually simple. However, it will need to drop and re-create the objects in the target system making it slow. Probably not a good choice for this problem.

• Using scripts and the SQL*Loader utility -- Similar to Export and Import, this method can be scripted and will work. However, the client already indicated that it takes too long so it is not appropriate. However, if we needed to get data from a third-party data source into Oracle, this would be a good way of doing it.

• Using Distributed Database and Replication -- While this method will ensure that data exists on both servers, based upon how distribution is configured, it may require permanent network connections and may generate a lot of traffic on the pipe between the head office and the regional offices. If you've tried to configure replication and/or distributed database with Oracle before, you'll know that this is not the easiest thing to do. In any case, it won't work for this client as regional offices do not have much available bandwidth, and they're not likely to get more in the short term.

• Using Transportable Tablespaces -- In Oracle 8i you can transport an entire tablespace, including its contents (tables, indexes, etc.), from one database to another. Because the data for the part numbers, bill of materials, and other required elements were contained on a single tablespace (or could be moved there by using Oracle 8i's ALTER TABLE … MOVE command) this was the chosen method. Let's look at this in more detail.

Oracle 8i's Tablespace Enhancements
Prior to Oracle 8i, management of extents allocated to segments was done by making changes to the data dictionary every time a new extent had to be allocated to a table, index or cluster. This created inefficiency as the act of allocating additional storage to an object required a hit on the SYSTEM tablespace, which now became a potential point of contention. Ways to avoid this were to carefully size extents and allocate them manually to minimize the hit.

• Same DB_BLOCK_SIZE for both databases.
• Same Character Set and National Language Character Set for both databases.
• Same operating system for both databases.
• The tablespace to be transported must not have any function-based indexes, scoped REFs, domain indexes or use any Oracle 8.0 advanced queuing functionality.
• All data must be self-contained: indexes and tables on the same tablespace, BLOBs and data segments on the same tablespace, parent and child tables in a primary key-foreign key relationship on the same tablespace (though you can choose not to transport all of the PK-FK data), all partitions for a table must be on the same tablespace, etc.
• The tablespace name must not exist on the target database. If it does, it needs to be deleted before the source tablespace is attached to the destination database.

1. Ensure that the data on the tablespace is self-contained. This is done by running the DBMS_TTS.TRANSPORT_SET_CHECK procedure passing it the name of the tablespaces you want to transport. This procedure populates a view called TRANSPORT_SET_VIOLATIONS that you can query to see if you have any errors that would cause the transport to fail. If you do, correct them and try again.
2. Generate a Transportable Tablespace Set that includes a list of all the objects on the tablespace that can be imported on the destination database. This is done by first altering the tablespace to be transported to READ ONLY and making a copy of the data files belong to the tablespace after doing so.
3. You then run the EXP utility to extract the data dictionary information to be imported into the destination database. To do this, pass the EXP utility the TRANSPORT_TABLESPACE=y parameter, along with the names of tablespaces to be transported, and whether or not to export constraints, security, and other settings. You now have a set of data files (with all the data) that can be copied to the destination database, as well as an Oracle export file that can used to create the data dictionary information about the objects in the datafiles.
4. Alter the tablespace back to READ WRITE mode in the source database so that the next round of updates can be started.
5. Transport the export file and data files to the destination database. You can use CD, tape, ZIP file or any other method that makes sense, based upon your data size.
6. If the tablespace to be attached to the destination database already exists, drop it along with its contents. This will be true where you are frequently updating data transferred from a central office to a branch office, as is the case here. Issue the DROP TABLESPACE command with the INCLUDING CONTENTS CASCADE CONSTRAINTS parameters.
7. Copy the data files from the source system to the hard disk on the destination (make a note of where they are as you will need this later).
8. Run the IMP utility to import data dictionary information. You need to specify the TRANSPORT_TABLESPACE=y parameter and the DATAFILES parameter, followed by the physical location of the datafiles on the destination computer's hard disk. If the users who own the objects in the tablespace do not exist in the destination database, you may also need to use the FROMUSER and TOUSER parameters. This is a relatively quick process, as only the data dictionary information about the objects on the tablespace is added since the data is already in the datafiles.

If you want to make modifications on the destination database, you can alter the tablespace to READ WRITE, although in this client's case we left the tablespace READ ONLY as all updates were made at the central office.