How to Transportable Tablespaces

Limitations
-Same OS
-The source and target database must use the same character set and national character set.
-No tablespace with same name must exists in destination
Transportable tablespaces do not support:
– Materialized views/replication
– Function-based indexes
– Scoped REFs
– 8.0-compatible advanced queues with multiple recipients
-COMPATIBLE initialization parameter required to be 8.1 or higher, 9.0, on the target, for different block sizes
-no references allowed from inside the set of tablespaces pointing outside of the tablespaces.

Check procedures
	-DBMS_TTS.TRANSPORT_SET_CHECK
		-requires EXECUTE_CATALOG_ROLE
		-You can optionally specify if constraints must be included, in this case set TTS_FULL_CHECK parameter to TRUE.
		
	-If you have never run before, please launch from sqlplus:
		dbms_tts.transport_set_check('mytablespace', true);
		This is to let the package perform some initializations
		
	-How to determine all the tablespaces used by a user, run as SYS with SQLplus:
		The following script will determine if an entire user may be exported using Transportable Tablespaces;	
		Run in source database
		When prompted for owner1, an example is: aldo
		owner2 may be blank
		
		set serveroutput on;
		declare
			result varchar2(500);
			cursor c is select distinct tablespace_name from  dba_segments  Where OWNER in (upper('&owner1'), upper('&owner2')) order by 1;
			rc c%rowType;
			errCount integer;
		begin
			
			open c;
			fetch c into rc;
			loop
				exit when c%notfound;
				if length(result) > 0 then result := result || ', ';
				end if;
				result := result ||  rc.tablespace_name ;
				fetch c into rc;
			end loop;
			close c; 
			dbms_output.put_line('Executing command: dbms_tts.transport_set_check('''|| result || ''', TRUE);');
			dbms_tts.transport_set_check('' || result || '', TRUE);
			select count(*) into errCount from TRANSPORT_SET_VIOLATIONS;
			if errCount = 0 then
				dbms_output.put_line('***********************');
				dbms_output.put_line('*Tablespace set is OK!*');
				dbms_output.put_line('***********************');
			else 
				dbms_output.put_line('*****************************************');
				dbms_output.put_line('*Violations reported, please run:       *');
				dbms_output.put_line('*SELECT * FROM TRANSPORT_SET_VIOLATIONS;*');
				dbms_output.put_line('*****************************************');
			end if;
		end;

Also check for any invalid object, especially for triggers:

select o.*
, 'ALTER '||object_type||' '|| owner||'.'||object_name||' COMPILE;' compile_script 
from dba_objects o
Where OWNER in (upper('&owner1'), upper('&owner2'))
and not status = 'VALID';

Troubleshooting

Determine all tablespaces used by a user

select distinct tablespace_name from  dba_segments  Where OWNER = upper('&username') order by 1;

See which objects for a user are on a tablespace

select * from  dba_segments  Where OWNER = upper('&username') and tablespace_name = upper('&tablespace_name')

Determine wich objects on tablespace do not belong to a user

select * from dba_segments
Where OWNER <> upper('&username') and tablespace_name = upper('&tablespace_name');

Move a table to a different tablespace

alter table aldo.prova move tablespace alworkshop;

Rebuild an index to a different tablespace

ALTER INDEX ALDO.SYS_C003960 REBUILD NOPARALLEL NOLOGGING TABLESPACE ALWORKSHOP

Generate script to move all objects of a user wich do not belong to a tablespace

select 
--dba_segments.OWNER, dba_segments.SEGMENT_NAME, dba_segments.PARTITION_NAME, dba_segments.SEGMENT_TYPE, 
--dba_segments.TABLESPACE_NAME, dba_segments.HEADER_FILE, dba_segments.HEADER_BLOCK, dba_segments.BYTES, 
--dba_segments.BLOCKS, dba_segments.EXTENTS, dba_segments.INITIAL_EXTENT, dba_segments.NEXT_EXTENT, 
--dba_segments.MIN_EXTENTS, dba_segments.MAX_EXTENTS, dba_segments.PCT_INCREASE, dba_segments.FREELISTS, 
--dba_segments.FREELIST_GROUPS, dba_segments.RELATIVE_FNO, dba_segments.BUFFER_POOL,
decode(segment_type, 'TABLE', 'alter table ' || dba_segments.owner ||'.'||dba_segments.segment_name||' move tablespace &tablespace_name;',
'INDEX', 'ALTER INDEX '||dba_segments.owner||'.'||dba_segments.segment_name||' REBUILD NOPARALLEL NOLOGGING TABLESPACE &tablespace_name;',
'LOBINDEX', 'ALTER TABLE ' || dba_lobs.owner ||'.'||dba_lobs.table_name||' move lob ('|| dba_lobs.column_name ||') store as (tablespace &tablespace_name);') sql_move 
--, dba_indexes.table_name table_of_index
from  dba_segments, dba_indexes, dba_lobs
Where dba_segments.OWNER = upper('&username') and dba_segments.tablespace_name <> upper('&tablespace_name')
and dba_indexes.owner (+)= dba_segments.owner and dba_indexes.index_name (+)= dba_segments.segment_name
and dba_lobs.owner (+)= dba_segments.owner and dba_lobs.index_name (+)= dba_segments.segment_name;

Be sure you have enough free space on destination host

Run this query on source db, and check on destination

select sum(bytes) bytes_total, sum(bytes)/1024/1024 MB_total, sum(bytes)/1024/1024/1024 GB_total, 
sum(bytes)/1024/1024/1024/1024 TB_total 
from dba_data_files where tablespace_name in (
	   select distinct tablespace_name from  dba_segments  Where OWNER in upper('&username')
) order by tablespace_name;

Generate a Transportable Tablespace Set
-make read only the required tablespaces
The following script makes Read Only all the tablespaces required to export a user using Transportable Tablespaces
Run in source database
When prompted for owner_name, an example is: aldo
When prompted for readmode, place ONLY
set serveroutput on;

declare
	cursor c(readmode varchar2) is select distinct tablespace_name, 'alter tablespace ' || tablespace_name || ' read '||
		    readmode as cmd from  dba_segments  Where OWNER in (upper('&owner1'), upper('&owner2')) order by 1;
	rc c%rowType;
	s varchar2(100);
begin
	if (&&only_script_generation) then
		dbms_output.put_line('************************');
	end if;
	open c('&&readmode');
	fetch c into rc;
	loop
		exit when c%notfound;
		if not(&&only_script_generation) then 
		   dbms_output.put_line('Making read only tablespace ' || rc.tablespace_name);
		end if;
		s := rc.cmd;
		if (&&only_script_generation) then 
		   dbms_output.put_line('select ''altering tablespace ' || rc.tablespace_name || ''' from dual;');
		end if;
		dbms_output.put_line(rc.cmd||';');
		if not(&&only_script_generation) then 
		   execute immediate rc.cmd;
		end if;
		if not(&&only_script_generation) then 
		   dbms_output.put_line('done.');
		end if;
		fetch c into rc;
	end loop;
	close c; 
	if (&&only_script_generation) then
		dbms_output.put_line('************************');
	end if;
end;

Troubleshooting

Check dba_locks for any locking session

select * from dba_locks
order by blocking_others

-run exp, only data dictionary (metadata) for the tablespaces is exported.
-If you are performing TSPITR or transport with a strict containment check, use: TTS_FULL_CHECK=Y
example: EXP TRANSPORT_TABLESPACE=y TABLESPACES=(sales_1,sales_2) TRIGGERS=y CONSTRAINTS=n GRANTS=n FILE=expdat.dmp
The following script only generate the export string to export a user using Transportable Tablespaces
Run in source database
When prompted for owner_name, an example is: aldo
When prompted for sys_connection_string you may insert sys/pwd@ora10:

set serveroutput on;
declare
			result varchar2(500);
			cursor c is select distinct tablespace_name from  dba_segments  Where OWNER in (upper('&owner_name_1_'), upper('&owner_name_2_')) order by 1;
			rc c%rowType;
			errCount integer;
			
			procedure out(msg varchar2) is
					  i number;
			begin
				 i := 1;
				loop
					dbms_output.put_line(substr(msg, i, 255));
					i := i + 255;
					exit when i > length(msg);
				end loop;
			end;
		begin
			
			open c;
			fetch c into rc;
			loop
				exit when c%notfound;
				if length(result) > 0 then result := result || ', ';
				end if;
				result := result ||  rc.tablespace_name ;
				fetch c into rc;
			end loop;
			close c; 
			out('--------------------------------------------------------------------------------');
			out('***for OS: IBM AIX, Oracle 9.2.0.5**********************************************');
			out('exp userid=''"&&sys_conn_source_string as sysdba"'' TRANSPORT_TABLESPACE=y TABLESPACES=''' || result || ''' TTS_FULL_CHECK=Y TRIGGERS=y CONSTRAINTS=y GRANTS=y FILE=expdat.dmp log=exp.log');
			out('********************************************************************************');
			out('***for OS: Windows, Oracle 9.2.0.1**********************************************');
			out('exp userid=''"&&sys_conn_source_string as sysdba"'' TRANSPORT_TABLESPACE=y TABLESPACES=(' || result || ') TTS_FULL_CHECK=Y TRIGGERS=y CONSTRAINTS=y GRANTS=y FILE=expdat.dmp log=exp.log');
			out('********************************************************************************');
			out('--------------------------------------------------------------------------------');
		end;

Drop tablespaces and datafiles on destination,

If you want to replace existing datafiles on destination, write down the datafiles path before dropping.

Run on destination

Drop tablespaces

set pagesize 2000;
select distinct 
--'drop tablespace ' || tablespace_name|| ' including contents and datafiles;' drop_script_with_datafiles,
'drop tablespace ' || tablespace_name|| ' including contents;' drop_script_no_datafiles
from  dba_segments  Where OWNER in (upper('&owner1'), upper('&owner2')) order by 1;

Drop any datafile

set pagesize 600;

select 
'rm '||file_name rm
--, 'rm -rf '||file_name rm_rf 
from dba_data_files where tablespace_name in (select distinct tablespace_name from  dba_segments  
	   Where OWNER in (upper('&owner1'), upper('&owner2'))
)

Troubleshooting

OS space has not been deallocated after delete

There may be some active processes, working on destination datafiles path, you may identify and kill them:

>fuser -c /oradata/aldo

You may eventually look up the Oracle session

-Transport both the datafiles and the export file of the tablespaces to a place accessible to the target database.
-Caution, place now the datafiles in the final destination path. For example in /opt/ora9/oradata/myora

You may receive help using:

select file_name 
from dba_data_files where tablespace_name in (
	   select distinct tablespace_name from  dba_segments  Where OWNER in upper('&username')
) order by tablespace_name;

Ftp script generation

This script mist be run in source database, but the resulting script must be run in destination host

Example parameters are:

owner1 = VCO
owner2=
ftp_parallel_sessions=4
ftp_source_host=myhost.fadalti.com
ftp_source_username=aldo
ftp_source_pwd=mypassword
dest_datafile_dir=/oradata/pvgg1_vco/
dest_export_file_dir=/home/oracle/admin/scripts/tts_vco
source_exp_dir_name=/home/oracle/admin/pvgh2/scripts/tts_vco

set serveroutput on size 50000;
declare 
	tablespaces varchar2(500);
	   dfiles varchar2(1000);
	   cursor c_tot_bytes is
	   		  select sum(bytes)  from dba_data_files d, dba_tablespaces t
	   		  where d.tablespace_name = t.tablespace_name 
			  and d.tablespace_name in (
			  	  select distinct tablespace_name from  dba_segments  
				  Where OWNER in (upper('&&owner1'), upper('&&owner2'))
			  );
	   
	   cursor c_tablespaces is 
	   		  select distinct tablespace_name from  dba_segments  
			  Where OWNER in (upper('&&owner1'), upper('&&owner2')) order by 1;
	   rc_tablespaces c_tablespaces%rowType;
	   cursor c_datafiles(m_tablespace_name varchar2) is 
	   		  SELECT  d.file_name, bytes
			  , substr(file_name, 1, instr(file_name, decode(instr(file_name, '\'), 0, '/', '\'), -1)) path 
			  FROM DBA_DATA_FILES d 
	   		  WHERE d.tablespace_name = m_tablespace_name; 
	   rc_datafiles c_datafiles%rowType;
	   
	   currDatafile varchar2(300);
	   systemSlash varchar2(1);
	   --File management
	   fileClosed boolean := true;
	   fileCount integer := 0;
	   exportFileDone boolean := false;
	   --bytes spanning
	   totBytes number;
	   avgBytes number;
	   currBytes number := 0;
	   
	   procedure closeFile is
	   begin
	   		dbms_output.put_line('close');
			dbms_output.put_line('quit');
			dbms_output.put_line('EOF');
			dbms_output.put_line('');
			fileClosed := true;
			fileCount := fileCount + 1;
	   end;
begin  
	   dbms_output.put_line('#!/bin/ksh'); 
	   open c_tot_bytes;
	   fetch c_tot_bytes into totBytes;
	   dbms_output.put_line('#Tot bytes ' || totBytes);
	   close c_tot_bytes;
	   avgBytes := totBytes / &&ftp_parallel_sessions; 
	   dbms_output.put_line('#Avg bytes per session ' || avgBytes);
	    
	 open c_tablespaces;
	 fetch c_tablespaces into rc_tablespaces;
	 loop
	 	 exit when c_tablespaces%notfound;
		 if length(tablespaces) > 0 then 
		 	tablespaces := tablespaces || ', ';
		 end if;
		tablespaces := tablespaces ||  rc_tablespaces.tablespace_name ;
		--Find datafiles for each tablespace 
		open c_datafiles(rc_tablespaces.tablespace_name);
		fetch c_datafiles into rc_datafiles;
		loop
			exit when c_datafiles%notfound;
				 --if length(dfiles) > 0 then 
				 	--dbms_output.put_line(' ');
			--end if;
			if currBytes = 0 then
	   		   dbms_output.put_line('nohup ftp -n -v '|| '&&ftp_source_host' || ' << EOF > output_ftp_' || fileCount ||'.log' || ' &');
	   		   dbms_output.put_line('user '|| '&&ftp_source_username'|| ' '|| '&&ftp_source_pwd');
	   		   dbms_output.put_line('bin');
			   fileClosed := false;
			end if;
			
			currDatafile := rc_datafiles.file_name;
			if(instr(currDatafile, '\')>0) then
				systemSlash := '\';
				else systemSlash := '/';
			end if;
			
			while(instr(currDatafile, systemSlash)>0) loop
									  currDatafile := substr(currDatafile, instr(currDatafile, systemSlash)+1, length(currDatafile));
		 	end loop;
			dbms_output.put_line('cd ' || rc_datafiles.path);
			dbms_output.put_line('lcd &&dest_datafile_dir');
			dbms_output.put_line('get ' || currDatafile);
			
			if not exportFileDone then
			   dbms_output.put_line('lcd &&dest_export_file_dir');
			   dbms_output.put_line('cd &&source_exp_dir_name');
			   dbms_output.put_line('get expdat.dmp');
			   exportFileDone := true;
			end if;
			
			dbms_output.put_line('');
			currBytes := currBytes + rc_datafiles.bytes;
			fetch c_datafiles into rc_datafiles;
			if (currBytes > avgBytes) then 
			   currBytes := 0;
			   closeFile;
			end if;
		end loop;
		close c_datafiles;
		fetch c_tablespaces into rc_tablespaces;
	end loop;
	close c_tablespaces;
	
	if not fileClosed then
	   closeFile;
	end if;
end;

Verify OS permissions over datafiles on destination

Tipically you need to remove read permissions to others:
>chmod o-r *.dbf

-imp on the other side
-Be sure no tablespaces with same name exists in destination

-Generate import script from source database

Example:
IMP TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=('/db/sales_jan','/db/sales_feb',...)
TABLESPACES=(sales_1,sales_2) TTS_OWNERS=(dcranney,jfee) FROMUSER=(dcranney,jfee) TOUSER=(smith,williams)

The following script only generate the import string to import an entire user using Transportable Tablespaces
Run in source database
When prompted for owner_name, an example is: aldo
When prompted for datafile_destination_path, an example is: /oradata/ora11/
When prompted for sys_connection_string you may insert sys/pwd@ora11:
When prompted for user_dest, an example is: aldo

set serveroutput on size 50000;
declare 
	tablespaces varchar2(500);
	   dfiles varchar2(1000);
	   cursor c_tablespaces is 
	   		  select distinct tablespace_name from  dba_segments  Where OWNER in (upper('&&owner1'), upper('&&owner2')) order by 1;
	   rc_tablespaces c_tablespaces%rowType;
	   cursor c_datafiles(m_tablespace_name varchar2) is SELECT  d.file_name FROM DBA_DATA_FILES d 
	   		  WHERE d.tablespace_name = m_tablespace_name; 
	   rc_datafiles c_datafiles%rowType;
	   cursor c_tts_owners is 
	   		  select distinct owner from dba_segments where tablespace_name in (
			  		 select distinct tablespace_name from  dba_segments  Where OWNER in (upper('&&owner1'), upper('&&owner2'))
			  );
	   rc_tts_owners c_tts_owners%rowtype;
	   errCount integer;
	   result varchar2(10000);
	   i integer;
	   currDatafile varchar2(300);
	   systemSlash varchar2(1);
	   tts_owners varchar2(200);
	   from_users varchar2(200);
	   to_users varchar2(200);
begin
	 open c_tablespaces;
	 fetch c_tablespaces into rc_tablespaces;
	 loop
	 	 exit when c_tablespaces%notfound;
		 if length(tablespaces) > 0 then 
		 	tablespaces := tablespaces || ', ';
		 end if;
		tablespaces := tablespaces ||  rc_tablespaces.tablespace_name ;
		--Find datafiles for each tablespace 
		open c_datafiles(rc_tablespaces.tablespace_name);
		fetch c_datafiles into rc_datafiles;
		loop
			exit when c_datafiles%notfound;
				 if length(dfiles) > 0 then dfiles := dfiles || ', ';
			end if;
			currDatafile := rc_datafiles.file_name;
			if(instr(currDatafile, '\')>0) then
				systemSlash := '\';
				else systemSlash := '/';
			end if;
			
			while(instr(currDatafile, systemSlash)>0) loop
									  currDatafile := substr(currDatafile, instr(currDatafile, systemSlash)+1, length(currDatafile));
		 	end loop;
			dfiles := dfiles || '''' || '&datafile_destination_path' || currDatafile || '''';
			fetch c_datafiles into rc_datafiles;
		end loop;
		close c_datafiles;
		fetch c_tablespaces into rc_tablespaces;
	end loop;
	close c_tablespaces; 
	--find tbalespace owners for this tablespace
		open c_tts_owners;
		fetch c_tts_owners into rc_tts_owners;
		loop
			exit when c_tts_owners%notfound;
			if length(tts_owners)>0 then
			   tts_owners := tts_owners || ', ';
			end if;
			tts_owners := tts_owners || rc_tts_owners.owner;
			fetch c_tts_owners into rc_tts_owners;
		end loop;
		close c_tts_owners;
	--FROMUSER
	from_Users := '&&owner1';
	if (not '&&owner2' is null)and(length('&&owner2')>0) then
	   from_users := from_users ||', '||'&&owner2';
	end if;
	--TOUSER
	to_Users := '&&user_dest1';
	if (not '&&user_dest2' is null)and(length('&&user_dest2')>0) then
	   to_users := to_users ||', '||'&&user_dest2';
	end if;
	result := ('imp userid=''"'||'&sys_dest_conn_string'||' as sysdba"'' TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES=' || dfiles || ' TABLESPACES='''|| tablespaces || ''' TTS_OWNERS="'||tts_owners||'" FROMUSER="'||from_users||'" TOUSER="'||to_users||'" log=imp.log');
	dbms_output.put_line('***for OS: IBM AIX, Oracle 9.2.0.5***');
	i := 1;
	loop
		dbms_output.put_line(substr(result, i, 255));
		i := i + 255;
		exit when i > length(result);
	end loop;
	dbms_output.put_line('*************************');
end;

-put the tablespaces back into READ WRITE mode
The following script makes read write all the tablespaces relative to a user
Run both in source and in destination database
set serveroutput on;

When prompted for readmode place WRITE

set serveroutput on size 50000; 
    	declare
	cursor c(readmode varchar2) is select distinct tablespace_name, 'alter tablespace ' || tablespace_name || ' read '||
		    readmode as cmd from  dba_segments  Where OWNER in (upper('&owner1'), upper('&owner2')) order by 1;
	rc c%rowType;
	s varchar2(100);
begin
	if (&&only_script_generation) then
		dbms_output.put_line('************************');
	end if;
	open c('&&readmode');
	fetch c into rc;
	loop
		exit when c%notfound;
		if not(&&only_script_generation) then 
		   dbms_output.put_line('Making read only tablespace ' || rc.tablespace_name);
		end if;
		s := rc.cmd;
		if (&&only_script_generation) then 
		   dbms_output.put_line('select ''altering tablespace ' || rc.tablespace_name || ''' from dual;');
		end if;
		dbms_output.put_line(rc.cmd||';');
		if not(&&only_script_generation) then 
		   execute immediate rc.cmd;
		end if;
		if not(&&only_script_generation) then 
		   dbms_output.put_line('done.');
		end if;
		fetch c into rc;
	end loop;
	close c; 
	if (&&only_script_generation) then
		dbms_output.put_line('************************');
	end if;
end;

Tablespaces quotas

If new tablespaces were added to destination, check quotas.

Troubleshooting

The following error may occur
		***
		Import: Release 9.2.0.5.0 - Production on Wed Apr 28 09:41:12 2004
		
		Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
		
		
		Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
		With the Partitioning option
		JServer Release 9.2.0.5.0 - Production
		
		Export file created by EXPORT:V09.02.00 via conventional path
		About to import transportable tablespace(s) metadata...
		import done in US7ASCII character set and AL16UTF16 NCHAR character set
		IMP-00003: ORACLE error 6502 encountered
		ORA-06502: PL/SQL: numeric or value error: character string buffer too small
		ORA-06512: at "SYS.DBMS_PLUGTS", line 1210
		ORA-06512: at line 1
		IMP-00000: Import terminated unsuccessfully
		***
		
		I caught this exception when using wrong parameters, for example when using:
			imp userid='"/ as sysdba"' TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES="'/oradata/pvgn1/PROVAEXP1_1.dbf', '/oradata/pvgn1/PROVAEXP1_2.dbf', '/oradata/pvgn1/PROVAEXP1_3.dbf', '/oradata/pvgn1/PROVAEXP2_1.dbf', '/oradata/pvgn1/PROVAEXP2_2.dbf', '/oradata/pvgn1/PROVAEXP2_3.dbf', '/oradata/pvgn1/PROVAEXP3.dbf','/oradata/pvgn1/PROVAEXP3_2.dbf', '/oradata/pvgn1/PROVAEXP3_3.dbf'" TABLESPACES="'PROVAEXP1, PROVAEXP2, PROVAEXP3'" TTS_OWNERS="aldo" FROMUSER="aldo" TOUSER="aldo" 
		instead of:
			imp userid='"/ as sysdba"' TRANSPORT_TABLESPACE=y FILE=expdat.dmp DATAFILES='/oradata/pvgi2/PROVAEXP1_3.dbf', '/oradata/pvgi2/PROVAEXP1_2.dbf', '/oradata/pvgi2/PROVAEXP1_1.dbf', '/oradata/pvgi2/PROVAEXP2_3.dbf', '/oradata/pvgi2/PROVAEXP2_2.dbf', '/oradata/pvgi2/PROVAEXP2_1.dbf', '/oradata/pvgi2/PROVAEXP3_3.dbf', '/oradata/pvgi2/PROVAEXP3_2.dbf', '/oradata/pvgi2/PROVAEXP3.dbf' TABLESPACES='PROVAEXP1, PROVAEXP2, PROVAEXP3' TTS_OWNERS="aldo" FROMUSER="aldo" TOUSER="aldo"

The following error may occur during user activity(the description is in italian):

ORA-00600: codice errore INT., argom.: [6122], [0], [1], [0], [], [], [], []

This is a bug caused by lob indexes, as a workaround I performed a traditional export/import, see Oracle Metalink