http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle8i_New_Features/ORA8i_17.shtml |
Transportable Tablespaces
by Jeff Hunter, Sr. Database Administrator
Contents
Overview
This article provides a brief introduction into configuring and using transportable tablespaces.
AND
Limitations of Transportable Tablespaces:
Explanation: The metadata exported from the target database does not contain enough information to create the user in the target database. The reason is that, if the metadata contained the user details, it might overwrite the privileges of an existing user in the target database.
(i.e. If the user by the same name already exists in the target database)
By not maintaining the user details, we preserve the security of the database.
In this example, we will be transporting the tablespaces, "FACT1, FACT2, and FACT_IDX" from a database named DWDB to REPORTDB. The user that owns these tables will be "DW" and password "DW".
Verify Self-Contained Status with the DBMS_TTS Package
Generate a Transportable Tablespace Set
Transport the Tablespace Set
In some cases this would be necessary if the files where copied off to a staging area in the previous step. Import the Tablespace Set
We now use the Import utility to bring the tablespace set's data-dictionary information into the target database.
The two required parameters are TRANSPORT_TABLESPACE=Y and DATAFILES='...' as in the following example: Final Cleanup
Oracle's Transportable Tablespace is one of those much awaited features that was introduced in Oracle8i (8.1.5) and is commonly used in Data Warehouses (DW). Using transportable tablespaces is much faster than using other utilities like export/import, SQL*Plus copy tables, or backup and recovery options to copy data from one database to another.
Introduction to Transportable Tablespaces
Before covering the details of how to setup and use transportable tablespaces, let's first discuss some of the terminology and limitations to provide us with an introduction.
Using Transportable Tablespaces
In this section, we finally get to see how to use transportable tablespaces. Here is an overview of the steps we will perform in this section:
essfully imported into the target database, they are in READ ONLY mode. If you intend to use the tablespaces for READ WRITE, you will need to manually alter them:
To verify that all tablespaces to transport are self-contained, we can use the TRANSPORT_SET_CHECK procedure within the DBMS_TTS PL/SQL Package. The first parameter to this procedure is a list of the tablespaces to transport. Keep in mind that all indexes for a table, partitions, and LOB column segments in the tablespace must also reside in the tablespace set. The second parameter to this procedure is a boolean value that indicates whether or not to check for referential integrity.
SQL> connect sys/change_on_install@dwdb as sysdba
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('fact1, fact2', TRUE);
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
VIOLATIONS
--------------------------------------------------------------------------------
Index DW.DEPT_PK in tablespace FACT_IDX enforces primary constriants of table D
W.DEPT in tablespace FACT1
Index DW.EMP_PK in tablespace FACT_IDX enforces primary constriants of table DW
.EMP in tablespace FACT1
OOOPS! As we can see from the above example, I forgot to include all tablespaces that will make this self-contained. In this example, I forgot to include the FACT_IDX tablespace. Let's correct that: SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('fact1, fact2, fact_idx', TRUE);
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
To generate a Transportable Tablespace Set, you will need to perform the following:
% sqlplus "sys/change_on_install@dwdb as sysdba"
SQL> ALTER TABLESPACE fact1 READ ONLY;
SQL> ALTER TABLESPACE fact2 READ ONLY;
SQL> ALTER TABLESPACE fact_idx READ ONLY;
SQL> exit
% exp
userid=\"sys/change_on_install@dwdb as sysdba\"
transport_tablespace=y
tablespaces=fact1,fact2,fact_idx
triggers=y
constraints=y
grants=y
file=fact_dw.dmp
% cp /u10/app/oradata/DWDB/fact1_01.dbf /u10/app/oradata/REPORTDB/fact1_01.dbf
% cp /u10/app/oradata/DWDB/fact2_01.dbf /u10/app/oradata/REPORTDB/fact2_01.dbf
% cp /u09/app/oradata/DWDB/fact_idx01.dbf /u09/app/oradata/REPORTDB/fact_idx01.dbf
% sqlplus "sys/change_on_install@dwdb as sysdba"
SQL> ALTER TABLESPACE fact1 READ WRITE;
SQL> ALTER TABLESPACE fact2 READ WRITE;
SQL> ALTER TABLESPACE fact_idx READ WRITE;
SQL> exit
To actually transport the tablespace, this is nothing more than copying (or FTP'ing) all tablespace set datafiles to be put in their proper location on the target database. In the section previous to this, we did that with the cp command in UNIX.
Before actually importing the tablespace(s) into the target database, you will need to ensure that all users that own segments in the imported tablespaces exist. For this example, the only user that owns segments in the exported tablespaces is DW. I will create this user:
% sqlplus "sys/change_on_install@reportdb as sysdba"
SQL> create user dw identified by dw default tablespace users;
SQL> grant dba, resource, connect to dw;
SQL> exit
% imp
userid=\"sys/change_on_install@reportdb as sysdba\"
transport_tablespace=y
datafiles='/u10/app/oradata/REPORTDB/fact1_01.dbf, /u10/app/oradata/REPORTDB/fact2_01.dbf, /u09/app/oradata/REPORTDB/fact_idx01.dbf'
file=fact_dw.dmp
When the tablespaces are suclearcase/" target="_blank" >cc
% sqlplus "sys/change_on_install@reportdb as sysdba" SQL> ALTER TABLESPACE fact1 READ WRITE; SQL> ALTER TABLESPACE fact2 READ WRITE; SQL> ALTER TABLESPACE fact_idx READ WRITE; SQL> exitPage Count: 261