Transprotable Tablespace using export/import

Transprotable Tablespace using export/import

Source

1. Create tablespace.

SQL> CREATE TABLESPACE TT_DATA
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA' SIZE 50M;

SQL> CREATE TABLESPACE TT_INDEX
DATAFILE 'F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA' SIZE 50M;


2. Create User.

SQL> create user ttbs identified by ttbs default tablespace tt_data temporary tablespace temp;

SQL> grant connect, resource to ttbs;

SQL> alter user ttbs quota unlimited on tt_index;


SQL> select name from v$tablespace where name like 'TT_%';

NAME
------------------------------
TT_DATA
TT_INDEX


SQL> select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';


TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA NO ONLINE
TT_INDEX NO ONLINE


3. Create table and index

SQL> create table ttbs.testtable (sno number, name varchar2(10)) tablespace tt_data;

SQL> insert into ttbs.testtable values (1001, 'Doyensys');

SQL> commit;

SQL> create index ttbs.i_testtable on ttbs.testtabale(sno) tablespace tt_index;



SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1


SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1


4.Source tablespace change read-only mode.

SQL>alter tablespace TT_DATA read only;

SQL>alter tablespace TT_INDEX only;


SQL> execute dbms_tts.transport_set_check('TT_DATA,TT_INDEX', TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected


select file_name from dba_data_files where tablespace_name in ('TT_DATA','TT_INDEX');


SQL> select file_name from dba_data_files
where tablespace_name in ('TT_DATA','TT_INDEX');

FILE_NAME
--------------------------------------------------------------------------------

F:\ORACLE\ORADATA\ORCL\TT_DATA01.ORA
F:\ORACLE\ORADATA\ORCL\TT_INDEX01.ORA


5. Export the metadata using transportable tablespace

exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=TT_DATA,TT_INDEX statistics=none


6. Copy TT_DATA01.ORA,TT_INDEX01.ORA datafiles and exp_tts.dmp to target server.

7 . Once copy is complete,Put source tablespace read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;



Target


7. create user.

create user ttbs identified by ttbs default tablespace user_data temporary tablespace temp;

grant connect, resource to ttbs;

8. import metadata


imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log fromuser=ttbs touser=ttbs transport_tablespace=y datafiles=F:\oracle\oradata\orcl\TT_DATA01.ORA,F:\oracle\oradata\orcl\TT_INDEX01.ORA


SQL>select tablespace_name,plugged_in,status from dba_tablespaces
where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES READ ONLY
TT_INDEX YES READ ONLY

9. target tablespace put read-write mode.

SQL> alter tablespace TT_DATA read write;

SQL> alter tablespace TT_INDEX read write;


SQL> select tablespace_name,plugged_in,status from dba_tablespaces
2 where tablespace_name like 'TEST%';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TT_DATA YES ONLINE
TT_INDEX YES ONLINE


SQL> select owner,count(*) from dba_segments
where tablespace_name in ('TT_DATA','TT_INDEX') group by owner;

OWNER COUNT(*)
------------------------------ ----------
TTBS 2

SQL> select tablespace_name,count(*) from dba_segments
where owner='TTBS' group by tablespace_name;

TABLESPACE_NAME COUNT(*)
------------------------------ ----------
TT_INDEX 1
TT_DATA 1


SQL> select segment_type,count(*) from dba_segments
where owner='TTBS' group by segment_type;

SEGMENT_TYPE COUNT(*)
------------------ ----------
INDEX 1
TABLE 1