From:
http://www.idevelopment.info/data/oracle/dba_tips/oracle8i_new_features/ora8i_17.shtml
Transportable TableSpaces, TASPORTABLESPACES
By Jeff Hunter, Sr. Database Administrator
Contents
Overview Introduction To Transportable TableSpaces Using Transportable TableSpaces
Overview
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 .
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.
The use of transportable tablespaces are much faster than using export / import, SQL * Plus copy tables, or backup and recovery options to copy data from one database to another A transportable tablespace set is defined as two components.:
All of the datafiles that make up the tablespaces that will be moved. AND An export that contains the data dictionary information about those tablespaces. COMPATIBLE must be set in both the source and target database to at least 8.1. When transporting a tablespace from an OLTP system to a data warehouse using the Export / Import utility, you will most likely NOT need to transport TRIGGER and CONSTRAINT information that is associated with the tables in the tablespace you are exporting. that is, you will set the TRIGGERS and CONSTRAINTS Export utility parameters equal to "N". The data in a data warehouse is inserted and altered under very controlled circumstances and does not require the same usage of constraints and triggers as a typical operational system does. It is common and recommended though that you use the GRANTS option By setting it to y. The triggers option is new in oracle8i for use with the export command. it is buy to control WHether Trigger Information, Associated Wi TH The Tables in A TableSpace, Are include in The TableSpace Transport.limitations of Transportable TableSpaces:
The transportable set must be self-contained. Both the source and target database must be running Oracle 8.1 or higher release. The two databases do not have to be on the same release The source and target databases must be on the same type of hardware and operating-system platform. The source and target databases must have the same database block size. The source and target databases must have the same character set. A tablespace with the same name must not already exist in the target database. materialized views, function- based indexes, scoped REFs, 8.0 compatible advanced queues with multiple-recipients, and domain indexes can not be transported in this manner. (As of Oracle8i) Users with tables in the exported tablespace should exist in the target database prior to initiating the import Create The user reported by the error message: The max information to create the user in the target database. The r eason is that, if the metadata contained the user details, it might overwrite the privileges of an existing user in the target database. (ie 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.using transportable TableSpaces
In this section, we finally get to see how to use transportable tablespaces. Here is an overview of the steps we willpeverage in this section:
.
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
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 INDICES WHETER OR NOT to Check for Referenceial 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
OOOOPS! As We can see from the Above Example, I forgot to include all tablespaces That Will Make this self-contained. In this Example, I forward 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
Generate a Transportable TableSpace Set
To Generate A
TRANSPORTABLE TABLESPACE SET, You Will Need To Perform The Following:
Place all tablespace within the tablespace set in READ ONLY mode. Use Export to gather tablespace data-dictionary information. Copy datafiles and the export dump from the source location to the target location. Place all tablespace within the tablespace set back to READ / WRITE. % 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/roadata/reportdb/FACT1_01.DBF
% cp /u10/app/oradata/dwdb/FACT2_01.DBF /U10/app/roadata/reportdb/FACT2_01.DBF
% CP /u09/app/roadata/dwdb/FACT_IDX01.DBF /U09/app/roadata/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
Transport The Tablespace Set
TO ACTUALLY
THIS Nothing More ThereSpace Set DataFiles To Be Put in Their Proper Location On The Target Database. in The section, We Did That with the
CP Command in UNIX.
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
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 isDW. I will create this User:
% SQLPLUS "SYS / CHANGE_ON_INSTALL @ reportdb as sysdba"
SQL> Create User DW Identified by DW Default TableSpace User;
SQL> Grant DBA, Resource, Connect to DW;
SQL> EXIT
We now 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 I Following Example:
% 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
Final Cleanup
When the Tablespaces Are Success, Imported Into The Target Database, They Are I, You Will Need To Manually ALTER THEM:
% 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
