REM Pre-create table TRANSPORT_SET_VIOLATIONS
REM --------------------------------------------------------------------------
REM In case TTS has not been used yet there will be the need to pre-create
REM table TRANSPORT_SET_VIOLATIONS    
REM below will raise an error but will create TRANSPORT_SET_VIOLATIONS
REM ʹÓÃDBMS_TTS±í¿Õ¼ä¼ì²é²»´æÔڵıí¿Õ¼äʵÏÖÌáÇ°´´½¨TRANSPORT_SET_VIOLATIONS±í¡£

exec dbms_tts.transport_set_check('non_existing_tablespace',TRUE);

REM tts_check
REM --------------------------------------------------------------------------
REM Below is a check if the set of TTS is possible, in other words:
REM Checks if a set of tablespaces (to be transported) is self-contained
REM in case errors are raised you have to check and resolve them

create or replace procedure tts_check(tablespace_names in varchar2)
is
cursor c_TRANSPORT_SET_VIOLATIONS is
  select violations from TRANSPORT_SET_VIOLATIONS;
begin
  dbms_output.put_line('Starting to check tablespaces as specified');
  DBMS_TTS.TRANSPORT_SET_CHECK(tablespace_names, TRUE, TRUE);
  for c_cur in c_TRANSPORT_SET_VIOLATIONS loop
    dbms_output.put_line(c_cur.violations);
  end loop;
  dbms_output.put_line('In case there are no line(s) after '||chr(39)||'Starting to check ...'||chr(39));
  dbms_output.put_line('It does imply that the check went fine and there are no issues to resolve.');
end;
/
show errors

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_check('USERS,TOOLS,MY_DATA1,MY_INDEX1');
exec tts_check('&tablespace_names');

SQL> exec tts_check('&tablespace_names');
Enter value for tablespace_names: USERS
Starting to check tablespaces as specified
ORA-39907: Index SUN.IDX_TEST1 in tablespace IND points to table SUN.TEST1 in
tablespace USERS.
In case there are no line(s) after 'Starting to check ...'
It does imply that the check went fine and there are no issues to resolve.
PL/SQL procedure successfully completed.

SQL> exec tts_check('&tablespace_names');
Enter value for tablespace_names: IND
Starting to check tablespaces as specified
ORA-39907: Index SUN.IDX_TEST1 in tablespace IND points to table SUN.TEST1 in
tablespace USERS.
In case there are no line(s) after 'Starting to check ...'
It does imply that the check went fine and there are no issues to resolve.
PL/SQL procedure successfully completed.
SQL> /
Procedure created.
SQL> exec tts_check('&tablespace_names');
Enter value for tablespace_names: USERS,IND
Starting to check tablespaces as specified
In case there are no line(s) after 'Starting to check ...'
It does imply that the check went fine and there are no issues to resolve.
PL/SQL procedure successfully completed.

column parameter format a30;
column value format a30;
select * from nls_database_parameters where parameter like '%CHARACTERSET%';

Set the correct NLS-characterset:
export NLS_LANG='.'
Set the correct ORA_NLS parameter:
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Env Variables explained
10.x: export ORA_NLS10=$ORACLE_HOME/nls/data
9.x:  export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
Expdp example:
--------------
connect / as sysdba
create directory tts_exp as '/u01/app2/tmp';
expdp_full_norows.par:
userid=system
directory=tts_exp
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
full=y
content=metadata_only
expdp parfile=expdp_full_norows.par
Check the logfile as created, it should not contain errors/warnings.
(for instance expdp does not support XMLSchema objects)

$> export DISPLAY=....
$> dbca &
The quickest way will be to use the template database as present in ORACLE_HOME. As mentioned before you have to use the same NLS settings of the original database. For the rest of the parameters you can use a copy of the one of the original database, which can be done after database creation

*In case of versions <</span> 10.2.0.x you might need to run the upgrade script in order to upgrade datadictionary to correct patch level

REM tts_exp_users
REM --------------------------------------------------------------------------
REM Below will show all users who do have objects in the tablespaces
REM to be transported. The output will be spooled to screen and will consist
REM of create user statements. The correct password/privileges/... will
REM be imported/set by the full export/import

--使用下面过程获取创建用户脚本，而权限和密码通过FULL导入处理。

create or replace procedure tts_exp_users(tablespace_names in varchar2)
is

string varchar2(4096);
type userlist is table of dba_users.username%type;
users userlist;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

dbms_output.put_line(string);
open c_cur for 'select distinct owner from dba_segments
where tablespace_name in ('||string||')';
fetch c_cur bulk collect into users;
close c_cur;
for i in users.first .. users.last
loop
dbms_output.put_line('create user '||users(i)||' identified by '||users(i)||';');
end loop;
end;
/

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_exp_users('USERS,TOOLS,MY_DATA1,MY_INDEX1');
SQL> exec tts_exp_users('&tablespace_names');

Enter value for tablespace_names: USERS,IND

'USERS','IND'

create user SUN identified by SUN;

PL/SQL procedure successfully completed.

REM tts_readonly
REM --------------------------------------------------------------------------
REM tts_readonly is a procedure to put all tablespaces as present in string
REM in read only mode. 
create or replace procedure tts_readonly(tablespace_names in varchar2)
is

string  varchar2(4096);
ts_fail integer:=0;

type tablespacetyp is table of dba_tablespaces%rowtype;
tslist tablespacetyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
  string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);



  open c_cur for 'select * from dba_tablespaces where tablespace_name in ('||string||')';
  fetch c_cur bulk collect into tslist;
  close c_cur;

  for i in tslist.first .. tslist.last
  loop
    if tslist(i).status!='ONLINE'
    then
      dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||' can NOT be put in read only mode, current status '||
tslist(i).status);
      ts_fail:=ts_fail+1;
    end if;
  end loop;

  if ts_fail!=0
  then
    dbms_output.put_line('Errors have been found while check if tablespace(s) can be put in read only mode');
    return;
  end if;

  for i in tslist.first .. tslist.last
  loop
    execute immediate 'alter tablespace '||tslist(i).tablespace_name||' read only';
  end loop;

end;
/

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_readonly('USERS,IND');

SQL> exec tts_readonly('&tablespace_names');

Enter value for tablespace_names: USERS,IND

PL/SQL procedure successfully completed.

SQL> SELECT tablespace_name,status FROM dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          READ ONLY

IND                            READ ONLY

Set the correct NLS-characterset:
$> export NLS_LANG='.'

Set the correct ORA_NLS parameter:
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Env Variables explained
10.x: export ORA_NLS10=$ORACLE_HOME/nls/data
9.x:  export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
--------------

expdp_tts.par:
userid=system
directory=tts_exp
dumpfile=expdp_tts.dmp
logfile=expdp_tts.log
transport_full_check=y
transport_tablespaces=(USERS,IND)


$> expdp parfile=expdp_tts.par

Check the logfile as created, it should not contain errors/warnings.

Set the correct NLS-characterset:
export NLS_LANG='.'
Set the correct ORA_NLS parameter:
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Env Variables explained
10.x: export ORA_NLS10=$ORACLE_HOME/nls/data
9.x:  export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
Expdp example:
--------------
connect / as sysdba
create directory tts_exp as '/u01/app2/tmp';
expdp_full_norows.par:
userid=system
directory=tts_exp
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
full=y
content=metadata_only
expdp parfile=expdp_full_norows.par
Check the logfile as created, it should not contain errors/warnings.
(for instance expdp does not support XMLSchema objects)

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_show_datafiles('USERS,IND');
SQL> exec tts_show_datafiles('&tablespace_names');
Enter value for tablespace_names: USERS,IND
Tablespace: IND File: /u01/app2/ora11/oradata/prim/index01.dbf
Tablespace: USERS File: /u01/app2/ora11/oradata/prim/users01.dbf

PL/SQL procedure successfully completed.

SQL>

Set the correct NLS-characterset:
$> export NLS_LANG='.'

Set the correct ORA_NLS parameter:
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Env Variables explained
10.x: export ORA_NLS10=$ORACLE_HOME/nls/data
9.x:  export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
--------------
connect / as sysdba
create directory tts_imp as '/u01/app2/tmp/';
impdp_tts.par:
userid=system
directory=tts_imp
dumpfile=expdp_tts.dmp
logfile=impdp_tts.log
transport_datafiles=('/u01/app2/ora11/oradata/prim/users01.dbf','/u01/app2/ora11/oradata/prim/index01.dbf')
[ora11@prim tmp]$ impdp parfile=impdp_tts.par
Import: Release 11.2.0.3.0 - Production on Sun Sep 13 23:39:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** parfile=impdp_tts.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:39:43

SQL> SELECT tablespace_name,status FROM dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

IND                            READ ONLY

USERS                          READ ONLY

SQL> SELECT count(1) FROM sun.test1;

  COUNT(1)

----------

     13657

SQL> SELECT tablespace_name,status FROM dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

IND                            READ ONLY

USERS                          READ ONLY

6 rows selected.

SQL> SELECT count(1) FROM sun.test1;

  COUNT(1)

----------

     13657

SQL> alter tablespace users read write;

Tablespace altered.

SQL> alter tablespace ind read write;

Tablespace altered.

SQL> DELETE FROM sun.test1 WHERE object_id > 100;

13558 rows deleted.

SQL> commit;

Commit complete.

SQL> SELECT count(1) FROM sun.test1;

  COUNT(1)

----------

        99

REM tts_readwrite
REM --------------------------------------------------------------------------
REM tts_readwrite is a procedure to put all tablespaces as present in string
REM in read only mode (only valid if status is read only)
create or replace procedure tts_readwrite(tablespace_names in varchar2)
is

string  varchar2(4096);
ts_fail integer:=0;

type tablespacetyp is table of dba_tablespaces%rowtype;
tslist tablespacetyp;

type cursor_ref is ref cursor;
c_cur cursor_ref;

begin
  string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

  open c_cur for 'select * from dba_tablespaces where tablespace_name in ('||string||')';
  fetch c_cur bulk collect into tslist;
  close c_cur;

  for i in tslist.first .. tslist.last
  loop
    if tslist(i).status!='READ ONLY'
    then
      dbms_output.put_line('Tablespace: '||tslist(i).tablespace_name||' can NOT be put in read write mode, current status '||tslist(i).status);
      ts_fail:=ts_fail+1;
    end if;
  end loop;

  if ts_fail!=0
  then
    dbms_output.put_line('Errors have been found while check if tablespace(s) can be put in read write mode');
    return;
  end if;

  for i in tslist.first .. tslist.last
  loop
    execute immediate 'alter tablespace '||tslist(i).tablespace_name||' read write';
  end loop;

end;
/

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_readwrite('USERS,,IND');
exec tts_readwrite('&tablespace_names');

REM tts_ts_users
REM --------------------------------------------------------------------------
REM tts_ts_users is a procedure extract the alter user statements in order
REM to correct the default + temporary tablespace of users of TTS
create or replace procedure tts_ts_users(tablespace_names in varchar2)
is

string varchar2(4096);
type userlist is table of dba_users.username%type;
users userlist;

type cursor_ref is ref cursor;
c_cur cursor_ref;

def_ts  dba_users.default_tablespace%type;
temp_ts dba_users.temporary_tablespace%type;

begin
  string:=chr(39)||upper(replace(replace(tablespace_names,' '),',',''','''))||chr(39);

  dbms_output.put_line(string);
  open c_cur for 'select distinct owner from dba_segments
                      where tablespace_name in ('||string||')';
  fetch c_cur bulk collect into users;
  close c_cur;
  for i in users.first .. users.last
  loop
    select default_tablespace,temporary_tablespace into def_ts,temp_ts
    from dba_users where username=users(i);
    dbms_output.put_line('alter user '||users(i)||' default tablespace '||def_ts||' temporary tablespace '||temp_ts||';');
  end loop;
end;
/

set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_ts_users('USERS,IND');
spool tts_exp_users_alter.sql
exec tts_ts_users('&tablespace_names');
spool off

Set the correct NLS-characterset:
$> export NLS_LANG='.'

Set the correct ORA_NLS parameter:
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Env Variables explained
10.x: export ORA_NLS10=$ORACLE_HOME/nls/data
9.x:  export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

imp parfile=imp_full_norows.par
--------------
connect / as sysdba
create directory tts_imp as '&directory_name';

impdp_full_norows.par:
userid=system
directory=tts_imp
dumpfile=expdp_full_norows.dmp
logfile=impdp_full_norows.log
full=y
content=metadata_only
table_exists_action=skip

impdp parfile=impdp_full_norows.par

connect / as sysdba
@?/rdbms/admin/utlrp.sql

15) OLD+NEW: Checking of objects per schema
Same code being used on OLD and NEW:

set pagesize 9999
spool check_objects_&old_new

column owner format a20;

select owner,object_type,count(*) from dba_objects
group by owner,object_type order by owner,object_type;

*) keep in mind that objects/grants/... made in SYS account are not exported
*) remember the procedures TTS_... will show a difference

$> stty cols 160
$> sdiff check_objects_old.lst check_objects_new.lst|more


16) OLD: stop the database, cleanup after a week/month/... after all is working fine.