Usage of Transportable Tablespace (Moving a Tablespace from one database to another)

Here is steps from moving TB from PROD to TEST

PROD


EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TS', incl_constraints => TRUE);

SELECT * FROM transport_set_violations;

ALTER TABLESPACE TS READ ONLY;

exp TRANSPORT_TABLESPACE=y TABLESPACES=TS FILE=TS.dmp

ALTER TABLESPACE TS READ WRITE;

Demo:
--------------------------------------------------------------------------------
SQL> EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TS', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected

SQL> ALTER TABLESPACE TS READ ONLY;

Tablespace altered.

[oracle10g1@cesora 10gR1_home]$ exp TRANSPORT_TABLESPACE=y TABLESPACES=TS FILE=TS.dmp

Export: Release 10.2.0.1.0 - Production on Mon Mar 2 17:30:14 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Username: system as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table EMP1
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
-------------------------------------------------------------------------------------

TEST

create user TS_TEST identified by TS_TEST;

imp TRANSPORT_TABLESPACE=y DATAFILES='/u02/oradata/TEST/TS_TEST01.dbf' TABLESPACES=TS
FILE=/u02/10gR1_home/TS.dmp

Demo:
-------------------------------------------------
[oracle10g@cesora TEST]$ cp ../PRD/TS01.dbf .
[oracle10g@cesora TEST]$ imp TRANSPORT_TABLESPACE=y DATAFILES='/u02/oradata/TEST/TS01.dbf'
TABLESPACES=TS FILE=/u02/10gR1_home/TS.dmp fromuser=TS touser=TS_TEST
[oracle10g@cesora TEST]$ pwd
/u02/oradata/TEST
[oracle10g@cesora TEST]$ ls -ltr
total 1391512
-rw-r----- 1 oracle10g dba 20979712 Feb 28 06:00 temp01.dbf
-rw-r----- 1 oracle10g dba 52429312 Mar 1 18:50 redo02.log
-rw-r----- 1 oracle10g dba 52429312 Mar 2 09:00 redo03.log
-rw-r----- 1 oracle10g dba 5251072 Mar 2 09:05 users01.dbf
-rw-r----- 1 oracle10g dba 104865792 Mar 2 09:05 rman01.dbf
-rw-r----- 1 oracle10g dba 104865792 Mar 2 09:05 example01.dbf
-rw-r----- 1 oracle10g dba 31465472 Mar 2 18:21 undotbs01.dbf
-rw-r----- 1 oracle10g dba 104865792 Mar 2 18:21 TS01.dbf
-rw-r----- 1 oracle10g dba 513810432 Mar 2 18:22 system01.dbf
-rw-r----- 1 oracle10g dba 377495552 Mar 2 18:22 sysaux01.dbf
-rw-r----- 1 oracle10g dba 52429312 Mar 2 18:22 redo01.log
-rw-r----- 1 oracle10g dba 7389184 Mar 2 18:22 control03.ctl
-rw-r----- 1 oracle10g dba 7389184 Mar 2 18:22 control02.ctl
-rw-r----- 1 oracle10g dba 7389184 Mar 2 18:22 control01.ctl
[oracle10g@cesora TEST]$ imp TRANSPORT_TABLESPACE=y DATAFILES='/u02/oradata/TEST/TS01.dbf'
TABLESPACES=TS FILE=/u02/10gR1_home/TS.dmp fromuser=TS touser=TS_TEST

Import: Release 10.2.0.1.0 - Production on Mon Mar 2 18:22:44 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: system as sysdba
Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing TS's objects into TS_TEST
. . importing table "EMP1"
Import terminated successfully without warnings.
[oracle10g@cesora TEST]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Mar 2 18:23:44 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: ts_test
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from emp1;

COUNT(*)
----------
1835008

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
TEST

ALTER TABLESPACE TS READ WRITE;
------------------------------------------------------------------

Now Trying to clone DB TEST to TS:


backup control file to trace in TEST

Shutdown TEST database

copy all the datafiles from TEST to target(TS)

startup nomount

run create control file script

alter database open reset logs

ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/oradata_TS/TS/temp01.dbf'SIZE 20971520 REUSE
AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

SQL> select status,instance_name from v$instance;

STATUS INSTANCE_NAME
------------ ----------------
OPEN TS