Oracle : TRANSPORTABLE TABLESPACE IN ORACLE

TRANSPORTABLE TABLESPACE IN ORACLE                                                            

 

Transportable Tablespace is used to move large amount of data between databases where the tablespace is  copied from source database to the target database.

TRANSPORTABLE BACKUPS:

• It is introduced in version 8i
• This backup is copying the whole tablespace from one DB to other along with the data
• This allows DBA to quickly copy n no of files from one DB to other.
• In version 8i, target database o/s and source database o/s must be same.
• In version 9i, oracle started supporting multi block size at tablespace level.
• In version 10g, oracle started supporting multi block size and multiple o/s.

Several different scenarios that can utilize transportable tablespace include:

• Restoring an unrecoverable database
• Migrating to a different OS.

NOTE: The tablespace name in the target database cannot have the same name in source database.

 

FIGURE  :

 

 

 

Segments present under system tablespace and any objects present in SYS cannot be transported because it contains all users, privileges, PL/SQL procedures.

Limitations:

1. It should not contain sys owned objects, temp segments, undo segments, dependent objects.
2. If any violations are found we need to drop or remove violations then only we can do transportation.

Package of dependent objects under particular tablespace = exec dbms_tbs_transport_set_check;

Package of dependents info stored under database objects (gives the reference of the objects  in the tablespace to be transported) = transport_set_violations;

 

EXAMPLES ON TRANSPORTABLE TABLESPACE:

 

1. Create tablespace tbs

 

2. Create user us and grant privileges to it:

 

3. alter the user us to set tablespace tbs as default tablespace:

 

4. Now connect to user us:

 

5. Create table t1 to user us and insert values into it:

 

 

6. Connect to sys user and check whether dependencies are present or not:

Package of dependent objects present under particular tablespace= exec dbms_tts.transport_set_check(‘TBS’)

 

 

7. Check whether any violations are present:

Package of dependent objects present under database objects= transport_set_violations

If any dependencies are present, then drop and execute.

 

 

8. alter tablespace to read only mode assuming no violations are present:

 

 

9. export the tablespace tbs metadata using exp utility:

 

 

10. Now on the client side perform import operation using import utility:

 

 

11. Switch the tablespace to read write mode:

 

 

12. Now check the tablespace is available at client side: