Transportable Tablespace (TTS)

'Transportable Tablespace' (TTS) first introduce in Oracle 8i and it become matured as time goes by.  You can use this  feature to copy a set of tablespaces from one Oracle Database to another.


For a DBA, transport data from one database to another is a very common task and you can do it in different ways

Method           Hardness     Best Suitable For    Availability

Database Link        Easy           Less than 50 GB           High
Data Pump           Moderate     Over 50 GB                 High
TTS                 Moderate     Terabytes of Data        Low
RMAN Duplicate    Hard           Full Database              High




Requirements:

• A tablespace must be totally self contained to be transportable.
• The source and target database must use the same character set and national character set.
• You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
• Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
• There are some limitations in encryption tables.

Lets see how it works.................
 
IN SOURCE DATABASE


1. Create two tablespaces and one user

sqlplus / as sysdba;

CREATE TABLESPACE TT01 DATAFILE '/oradata/tt_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 8K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;

CREATE TABLESPACE TT02 DATAFILE '/oradata/tt_02.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;


CREATE USER test identified by test default tablespace tt01;
GRANT connect,resource to test;

2. Create a table with some data

conn test/test;

CREATE TABLE test_data(
id number (10),
alphabate varchar2 (10)
) TABLESPACE tt01;

ALTER TABLE test_data ADD CONSTRAINT PK_TEST_DATA_ID PRIMARY KEY (ID) using index tablespace tt02;

INSERT INTO test_data VALUES (1,'A');
INSERT INTO test_data VALUES (2,'B');
INSERT INTO test_data VALUES (3,'C');
INSERT INTO test_data VALUES (4,'D');
INSERT INTO test_data VALUES (5,'E');

commit;

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME       TABLESPACE_NAME
------------------ ------------------
TEST_DATA          TT01
PK_TEST_DATA_ID    TT02

So we have a table in one tablespace and an unique index (creat for primary key) in another tablespace.



3. tablespace's 'self contained' Test

conn / as sysdba;

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

SELECT * FROM sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index TEST.PK_TEST_DATA_ID in tablespace TT02 enforces primary constraints  of table TEST.TEST_DATA in tablespace TT01.

we got this error because TT01 tablespace contain a table which has an index stored in another tablespace.

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

SQL> SELECT * FROM sys.transport_set_violations;

no rows selected

This time no Error because all two tablespaces are included. Now make them 'Read Only'.


ALTER TABLESPACE tt01 READ ONLY;
ALTER TABLESPACE tt02 READ ONLY;


4. Take metadata of those 2 tablespaces by using datapump

expdp system directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/ ******** directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /d01/dump_dir/trans_tablespaces.dmp
******************************************************************************
Datafiles required for transportable tablespace TT01:
  /oradata/tt_01.dbf
Datafiles required for transportable tablespace TT02:
  /oradata/tt_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 02:27:17


5. Copy the datafiles to destination database

scp tt_01.dbf oracle@db2:/oradata/
scp tt_02.dbf oracle@db2:/oradata/

6. Make the read_only tablespaces to read write mode

sqlplus / as sysdba

ALTER TABLESPACE tt01 READ WRITE;
ALTER TABLESPACE tt02 READ WRITE;


IN DESTINATION DB

1. Create a user same as source DB

sqlplus / as sysdba;

CREATE USER test identified by test ;
GRANT connect,resource to test;


2. Import the metadata

impdp system directory=dump_dir dumpfile=trans_tablespaces.dmp transport_datafiles='/oradata/tt_01.dbf','/oradata/tt_02.dbf' logfile=imp_trans_tablespaces.log


3. Verify Tablespace and Object Movement

conn test/test

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
TEST_DATA            TT01
PK_TEST_DATA_ID      TT02

SELECT * FROM TEST_DATA;

        ID ALPHABATE
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E

Well we are done! To know more about TTS please go through the Oracle Doc http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm

CTAS : Create Table as

We all are very familiar with CTAS or 'Create Table as' clause that used to construct new table and populated it with data extract by 'Select' statement. That means table creation and population are done by executing a single sql CTAS statement.

Fom example,

CREATE TABLE employee_info As
SELECT emp.id, emp.name, dp.name FROM employee emp, department dp WHERE emp.dp_id =dp.id;

The above CTAS statment create a new table named 'employee_info' and store the result of SELECT statement into that table.

Here are some common use of CTAS statement:

• Store the result of select statement into a table.
• create a duplicate table for backup purpose.
• Reorganize existing table for better performance.

There are some limitations of CTAS statement:

• Can't create indexes ( in case of table duplication or Reorganization).
• Can't create primary, unique or foreign key constraints ( in case of table duplication or Reorganization). But it can create 'NOT NULL' constraint.
  

• Though CTAS mirrors metadata structure, it can't mirror 'DEFAULT' value checker for columns.

I have learned the 3rd pitfall of CTAS recently. I used CTAS to construct a duplicate table ( a sub task of table partitioning). The partitioning job was successful but the problem arose when we inserted new rows into the partitioned table. One of the column got null value stored instead of zero (the column's default value was zero). Through an investigation we discover that CATS statement skipped default value check of that column.