Use Transportable Tablespace to move data

Transportable Tablespaces

 You can use the transportable tablespaces feature to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases. The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the target database's standard block size. Transporting tablespaces is particularly useful for:

Moving data from OLTP systems to data warehouse staging systems Updating data warehouses and data marts from staging systems Loading data marts from central data warehouses Archiving OLTP and data warehouse systems efficiently Data publishing to internal and external customers Performing Tablespace Point-in-Time Recovery (TSPITR) 

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

SOURCE DATABASE STEPS

1.     CREATE USER,TABLESPACE AND TABLE IN SOURCE DATABASE

CONN / AS SYSDBA

CREATE TABLESPACE test

                        DATAFILE '/u01/DATAFILE/sourceDB/test.dbf'

                        SIZE 10M AUTOEXTEND ON NEXT 1M;

CREATE USER test IDENTIFIED BY test

                        DEFAULT TABLESPACE test

                        TEMPORARY TABLESPACE temp

                        QUOTA UNLIMITED ON test;

GRANT CONNECT,RESOURCE TO test;

CONN test/test

CREATE TABLE T1 (

                                                            Tid          NUMBER,

                                                            Tdesc VARCHAR2(50),

                                                            CONSTRAINT t1_pk PRIMARY KEY (Tid)

);

INSERT /*+ APPEND */ INTO T1 (Tid, Tdesc)

SELECT level,

                        'Description for ' || level

FROM   dual

CONNECT BY level <= 10000;

COMMIT;

2.      VERIFY THAT TABLESPACE TEST IS A CANDIDATE TO BE TRANSPORED

   

For a tablespace to be able to transport it shouhd store all objects of schema object i.e. table,view, index and constraints etc, and to check this, TRANSPORT_SET_CHECK procedure is used to verify

CONN / AS SYSDBA

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

PL/SQL procedure successfully completed.

Above procedure execution will populate the view TRANSPORT_SET_VIOLATIONS if there is any violation i.e. any object exist outside tablespace for specific schema

SELECT * FROM transport_set_violations;

no rows selected

Means no violations.

3.     EXPORT SOURCE TABLESPACE METADATA FOLLOWING BELOW STEPS

If database is 10gR2 or older then tablespace needs to be in read only state and for 11gR1/R2 it can be exported online.

SQL> ALTER TABLESPACE test READ ONLY;

Tablespace altered.

expdp userid=system/password directory=dbbkp transport_tablespaces=test dumpfile=test.dmp logfile=test.log

4.     NOW COPY DATAFILE BELONGS TO TABLESPACE TEST FOR FUTURE USE

cp /u01/datafile/sourceDB/test.dbf /u01/DBBKP*

Here DBBKP is backup location and sourceDB is source database name.

5.     SWITCH BACK TABLESPACE TO READ WRITE

ALTER TABLESPACE test READ WRITE;

Tablespace altered.

TARGET DATABASE STEPS

1.     CREATE JUST USER IN TARGET DATABASE AND DON'T CREATE TABLESPACE

CONN / AS SYSDBA

CREATE USER test IDENTIFIED BY test;

GRANT CREATE SESSION, CREATE TABLE TO test;

CONN / AS SYSDBA

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

2.     PASTE THE DATAFILE COPIED FROM SOURCE DATABASE TO TARGET DATABASE'S DATA FILE LOCATION I.E.

cp /u01/DBBKP/test.dbf /u01/datafile/sourceDB/

3.     IMPORT METADATA TO TARGET DATABASE WHICH WAS EXPORTED EARLIER FROM             SOURCE DATABASE

impdp userid=system/password directory=dbbkp dumpfile=test.dmp logfile=test.log transport_data_files

4.     NEWLY CREATED TABLESPACE WILL BE IN READ ONLY MODE AS CAN BE SEEN VIA   QUERY BELOW

SELECT tablespace_name, plugged_in, status

FROM   dba_tablespaces

WHERE  tablespace_name = 'TEST';

TABLESPACE_NAME                PLU STATUS

------------------------------                  ---     ---------

TEST                                        YES READ ONLY

5.     SWITCH TABLESPACE TO READ WRITE

SQL> ALTER TABLESPACE test READ WRITE;

NOW TABLESPACE CAN BE READ AND WRITE AND AVAILABLE FULLY FUNCTIONAL AT TARGET DATABASE.