transportable tablespace example

-          Create tablespace

Sql> create tablespace tts datafile ‘/u01/app/oracle/oradata/orcl/tts01.dbf’ size 100M;

Create user with tts as default tablespace

Sql> create user demouser identified by demouser default tablespace tts;

Sql>grant connect,resource to demouser;

Connect to user:

Sql>conn demouser

Password:*******

Create table

Sql>create table demotable(num number);

Inset some rows into the table.

Exporting:

Put the tablespace into read only mode

Sql>alter tablespace tts read only;

Export using transportable tablespace

$exp file=/u01/backup/tts.dmp tablespaces=tts transport_tablespace=y;

Username: system as sysdba

Password:*******

-copy the tablespace datafiles to backup location

Importing:

-copy the datafiles to to the location where ever you want

-import dumpfile

$imp file=/u01/backup/tts.dmp transport_tablespace=y datafiles=’/u01/app/oracle/tts01.dbf’

Username: system as sysdba

Password:*******

-Check the tablespace in database

Sql>select name from v$tablespace;

check table in user

  

sql> conn ttsuser

password:ttsuser

sql>select * from tab;