Oracle 8i Transportable Tablespaces
Ivan Strand
July 20, 1999
Description
-------------------------------
Transportable tablespaces can be used to copy a set of tables from one Oracle database to another. This approach to replication has been used for feeding data from OLTP systems to data warehouse systems, updating data warehouses and data marts from staging systems, loading data marts from central data warehouses, archiving OLTP and data warehouse systems efficiently and data publishing to internal and external customers.
Transportable tablespaces is a new feature of Oracle 8i. There are limitations in using this method of replication.
Limitations:
Step 1: Pick a self-contained set of tablespaces.
It is possible to transport tablspaces that have no references from inside the tablespaces to an object ouside of the tablespace. An example is an index in a set of tablespaces for a table that is outside of the set of tablespaces being transported. Partitions and referential integrity constraints can also cause violations of the self-contained requirement.
There is an Oracle utility PL/SQL procedure that will check whether or not a tablespace or set of tablespaces are self-constained. See Appendix: Checking for Self-Contained Tablespaces
Step 2: Generate a transportable tablespace set.
First all tablespaces being copied must be placed in read-only mode using the ALTER TABLESPACE command. Second, Export is invoked to extract the metadata about the tables being copied for the tablespace set.
Step 3: Transport the tablespace set.
The export dump file and the datafiles are physically copied to the environment of the target database. This unix-level copy may be accomplished using file copy (if the target and source machines are physically connected) or through a network-based copy such as ftp or secure copy.
Step 4: Plug in the tablespace set.
Once the datafiles are placed in a location where the target database can access them Import is executed in Transport-tablespace mode to plug in the tablespace set. As mentioned, the target tablespace name must be the same name as was exported from the source database. User names do not have to be the same. The owners of the tables may be changed in the target database by using the Import FROMUSER/TOUSER parameters.
OBJECTS Which Are Transportable:
A transportable tablespace can contain the following objects: tables, index, bitmap indexes, index-organized tables, LOB's, nested tables, varrays and tables with user-defined columns.
OBJECT Behavior and Limitations:
Some objects do not behave normally when transferred using transportable tablespaces. The list is presented here with a short description of the exceptional behavior.
ROWIDs
When a database contains tablespaces that have been plugged in (from other databases), the ROWIDs in that database are no longer unique. A ROWID is guaranteed unique only within a table.
Advanced Queues
You can use transportable tablespaces to move or copy Oracle advanced queues, as long as these queues are not 8.0-compatible queues with multiple recipients. After a queue is transported to a target database, the queue is initially disabled. After making the transported tablespaces read-write in the target database, you can enable the queue by starting it up via the built-in PL/SQL routine dbms_aqadm.start_queue().
Indexes
You can transport regular indexes and bitmap indexes. When the transportable set fully contains a partitioned table, you can also transport the global index of the partitioned table.
Function-based indexes and domain indexes are not supported. If they exist in a tablespace, you must drop them before you can transport the tablespace.
Triggers
Triggers are exported without a validity check. In other words, Oracle does not verify that the trigger refers only to objects within the transportable set. Invalid triggers will cause a compilation error during the subsequent import.
Snapshots/Replication
Transporting snapshot or replication structural information is not supported. If a table in the tablespace you want to transport is replicated, you must drop the replication structural information and convert the table into a normal table before you can transport the tablespace.
REFs
REFs are not checked when Oracle determines if a set of tablespaces is self-contained. As a result, a plugged-in tablespace may contain dangling REFs. Any query following dangling REFs returns a user error.
Privileges
Privileges are transported if you specify GRANTS=y during export. During import, some grants may fail. For example, the user being granted a certain right may not exist, or a role being granted a particular right may not exist.
Partitioned Tables
You cannot move a partitioned table via transportable tablespaces when only a subset of the partitioned table is contained in the set of tablespaces. You must ensure that all partitions in a table are in the tablespace set,
OTHER LIMITATIONS and CAUTIONS:
Advanced Queues
You can use transportable tablespaces to move or copy Oracle advanced queues, as long as these queues are not 8.0-compatible queues with multiple recipients. After a queue is transported to a target database, the queue is initially disabled. After making the transported tablespaces read-write in the target database, you can enable the queue by starting it up via the built-in PL/SQL routine dbms_aqadm.start_queue().
DBA-level Authority
Execution of the Transportable Tablespace function requires DBA authority and ownership of the underlying data files.
New Feature
Transportable Tablespace is a new feature in Release 8i with the risk associated with all Version 1 products.
Upward Compatibility / Mixed Oracle Versions
Because Transportable Tablespaces is platform and operating-system dependent, there may be additional concerns compatibility across Oracle release levels. For example, will there be upward compatibility from Release 8.1.5 (Release 8i) and above? Or must the release levels be identical between the source and the target for Transportable Tablespaces?
Possible Applications
-------------------------------
Transport tablespace could possibly be used for replication of data to create the ODS for some or all of the OLTP applications supporting DSS access.
Additionally, the transport tablespace approach could be used to quickly and efficiently stage Oracle tables for the manipulation required for building datagroupd.
Implementation
-------------------------------
Using Transport Tablespace to create the ODS.
The advantage of transport tablespace is the speed of transfer. A combination of UNIX file copy and Export/Import is used to copy the table data and metadata out of the source database and into the target database.
Implementation issues involve the organization of the tablespaces being replicated. An application area may use a single tablespace for data tables and a separate tablespace for indexes to those tables. In this example transport tablespace may or may not be feasible depending on the number of tables in the tablespaces that are not required in the DSS database. If on the other hand more tablespaces are used to contain the large number of application tables it may be more feasible to copy a set of tablespaces to the DSS environment even though all of the tables will not be used for an ODS or datagroups. The efficiency of transfer may compensate for any overhead occuring when un-needed tables are transferred.
Using Transport Tablespace to stage DSS data.
Because of the increased efficency of transfer, it may be desirable to copy sets of tables to a staging or assembly area in the DSS environment for subsequent manipulation and extraction. Using transportable tablespaces in this way may help quickly capture the OLTP data and permit asynchronous/parallel processing of that table data. This parallel processing of different groups of application tables may help reduce the pressure of the limited batch window.
Some or all of these transfers may be the equivalent of building the ODS for an application. The point of distinction here is that of asynchronous processing of the separate groups of tables.
Whether an ODS or a non-ODS group of application tables, the contents of the source tablespaces would be very important in using transportable tablespaces to stage table data for further processing. If many tables are held in few tablespaces, transferring tablespaces would require large disk-storage capabilites for the temporary staging of that data. If many of the tables in a tablespace are not needed to build the DSS objects then transferring tables at the tablespace level could be very inefficient with regard to disk space.
And finally, space allocation within each tablespace could be an issue. If some or many of the tablespaces are allocated with a significant amount of free space then transferring tablespaces could require a conisiderable amount of free space on the target DSS machine or node to accomodate the physical file transfer.
Evaluation
-------------------------------
Transportable tablespaces could offer enhanced performance in transferring data between Oracle database environments. The value in this method will come more into focus as we better understand the schema organization, the number of tablespaces and their contents. The transportable tablespace method should be kept in mind when the application and DSS environment data modeling and database designs are carried out.
Recommendation
-------------------------------
A careful effort should be made to document the performance difference between transportable tablespaces and other replication methods. It may be that other methods can be used in concert with transportable tablespaces to obtain a result that blends the efficiency of UNIX-level file transfer with the granularity of control that is possible when using Export/Import or other methods to replicate data at the table and schema levels.
Transportable tablespaces should be studied and tested as a means of replicating Oracle data out of the OLTP environment into the DSS environment. More needs to be known about the organization of the tablespace containers that will hold the application tables.
-------------------------------
Appendix: Checking for Self-Contained Tablespaces
Suppose you want to determine whether tablespaces ts1 and ts2 are self-contained (with constraints taken into consideration). You can issue the following command: execute dbms_tts.transport_set_check('ts1,ts2', TRUE)
Here, transport_set_check is a PL/SQL routine in the PL/SQL package DBMS_TTS, with the following prototype:
PROCEDURE transport_set_check(ts_list IN varchar2, incl_constraints IN boolean)
ts_list - list of tablespace names separated by comma incl_constraints - TRUE if one would like to take constraints into consideration. FALSE otherwise.
After invoking this PL/SQL routine, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view will be empty. If the set of tablespaces is not self-contained, this view lists all the violations.
Documentation Source Material:
Chapter 9: Managing Tablespaces Oracle8i Administrator's Guide Release 8.1.5 A67772-01