Friday, March 14, 2008

An Example of Transportable Tablespaces

(set oracle environment for source DB)
At source DB:
connect / as sysdba
create tablespace data4 datafile
'/path1/data401.dbf' size 10m autoextend on next 10m,
'/path1/data402.dbf' size 10m autoextend on next 10m;
create tablespace indx4 datafile
'/path1/indx401.dbf' size 10m autoextend on next 10m;
create user tc identified by tc default tablespace data4
temporary tablespace temp quota unlimited on data4 quota unlimited on indx4;
grant create session, create table to tc;
create table tc.testtab (nr number, txt varchar2(10)) tablespace data4;
insert into tc.testtab values (1, 'line 1');
commit;
create index tc.i_testtab on tc.testtab(nr) tablespace indx4;
select owner'.'segment_name "OWNER.SEGMENT_NAME", segment_type, tablespace_name from dba_segments where tablespace_name in ('DATA4','INDX4');
alter tablespace data4 read only;
alter tablespace indx4 read only;
execute dbms_tts.transport_set_check('data4,indx4', TRUE);
select * from transport_set_violations;
exit;

At Shell:
exp "'/ as sysdba'" file=exp_tts.dmp log=exp_tts.log transport_tablespace=y tablespaces=data4,indx4
cp /path1/data401.dbf /path2/data401.dbf
cp /path1/data402.dbf /path2/data402.dbf
cp /path1/indx401.dbf /path2/indx401.dbf
cd /path2
chmod g+w *

(set oracle environment for destination DB)
At destination DB:
connect / as sysdba
create user tcnew identified by tcnew default tablespace tools temporary tablespace temp;
grant create session, create table, resource to tcnew;

At Shell:
imp "'/ as sysdba'" file=exp_tts.dmp log=imp_tts.log fromuser=tc touser=tcnew transport_tablespace=y datafiles=/path2/data401.dbf,/path2/data402.dbf,/path2/indx401.dbf

At destination DB:
connect / as sysdba
alter tablespace data4 read write;
alter tablespace indx4 read write;
alter user tcnew quota unlimited on data4;
alter user tcnew quota unlimited on indx4;
revoke resource from tcnew;
select owner'.'segment_name "OWNER.SEGMENT_NAME", segment_type, tablespace_name from dba_segments where tablespace_name in ('DATA4','INDX4');

No comments: