Using data Pump to export and import in case of characterset conversion and in case of no characterset conversion

I want to share 2 cases where we want to export and import into the same character set and export and import into different character set.

NO Character set Conversion:

We export the data from production as follows:
1-Create a parfile and name it ADAPTER.PAR

=================================
Contents of the file:
=================================
schemas=ADAPTER
dumpfile=ADAPTER-20-9-2012.dmp
directory=DPUMP_DIR3
logfile=USG-20-09-12.log
FLASHBACK_SCN=7890710186518

================================

Then export  from the database as follows:
C:\expdp mazab@CCDB parfile='ADAPTER.PAR'

transfer the file using scp command:

scp /filepath/ADAPTER-20-09-2012.dmp oracle@Your_IP:/data/DB/TIMDB/DPUMP
=======================================================
=======================================================
Then import the dump as follows:
========================================================
login to TIMDB:
export ORACLE_SID=TIMDB

impdp directory=TIM_DATA_PUMP dumpfile=ADAPTER-9-5-2011.dmp logfile=ADAPTER-9-5-2011.log schemas=adapter REMAP_SCHEMA=adapter:adapters_ea4 remap_tablespace=ADAPTER:TIM_EA3

Please consider the tablespaces on production and remap them in testing.

 Characterset Conversion:


We export the data from production as follows:
1-Create a parfile and name it SUB.PAR
2-Get scn from the production database to avoid parent child constraints error when importing into TIMDB.
=================================
Contents of the file:
=================================
schemas=SUB
dumpfile=SUB-20-09-2012.dmp
directory=DPUMP_DIR3
logfile=SUB20-09-12.log
QUERY =SUB.SMS_ARCHIVE:"where 1=2"
QUERY =SUB.REQUEST_PARAMETER_ARCHIVE:"where 1=2"
QUERY =SUB.REQUEST_ARCHIVE:"where 1=2"
QUERY =SUB.MOBILE_TRADER_STG:"where 1=2"
QUERY =SUB.TRANSACTION:"where 1=2"
QUERY =SUB.TRANSACTION_PARAMETERS:"where 1=2"
QUERY =SUB.Transaction_Archive:"where 1=2"
QUERY =SUB.Request:"where 1=2"
QUERY =SUB.SMS:"where 1=2"
QUERY =SUB.Transaction_Parameters_Archive:"where 1=2"
QUERY =Mobile_trader:"where 1=2"
FLASHBACK_SCN=7882306652406

================================

Then export  from the database as follows:
C:\expdp mazab@RSDSDB parfile='SUB.PAR'

transfer the file using scp command:

scp /filepath/SUB-20-09-2012.dmp oracle@Your_IP:/data/DB/TIMDB/DPUMP
=======================================================
=======================================================
Then import the dump as follows:
========================================================
login to TIMDB:
export ORACLE_SID=TIMDB

impdp directory=TIM_DATA_PUMP dumpfile=SUB-20-09-2012.dmp logfile=SUB-20-09-2012.log schemas=sub REMAP_SCHEMA=sub:SUB_EA4 remap_tablespace=sub_idx:TIM_EA3,sub:TIM_EA3 content=metadata_only exclude=index exclude=constraint exclude=statistics exclude=trigger exclude=grant
==============================================
==============================================
SQL> drop table semantics$;
SQL>create table semantics$(s_owner varchar2(40),
                       s_table_name varchar2(40),
                       s_column_name varchar2(40),
                       s_data_type varchar2(40),
                       s_char_length number)
===================================================
@insert.sql
@cursor.sql
drop table semantics$;
==================================================
impdp directory=TIM_DATA_PUMP dumpfile=SUB-20-09-2012.dmp logfile=SUB-20-09-2012.log schemas=sub REMAP_SCHEMA=SUB:sub_EA4 remap_tablespace=sub_idx:TIM_EA3,sub:TIM_EA3 content=data_only
=====================================================
impdp directory=TIM_DATA_PUMP dumpfile=SUB-SUB-20-09-2012.dmp logfile=SUB-20-09-2012.log schemas=sub REMAP_SCHEMA=SUB:sub_EA4 remap_tablespace=sub_idx:TIM_EA3,sub:TIM_EA3 content=metadata_only include=index include=constraint include=statistics include=trigger transform=storage:n
============================================
Contents of the cursors:
==========
@insert.sql
============
insert into semantics$
  select C.owner, C.table_name, C.column_name, C.data_type, C.char_length
    from all_tab_columns C, all_tables T
  where C.owner = T.owner
    and T.owner = upper('&&schema')
    and C.table_name = T.table_name
    and C.char_used = 'B'
-- only need to look for tables who are not yet CHAR semantics.
--  and T.partitioned != 'YES'
-- exclude partitioned tables  (removed condition!)
    and C.table_name not in (select table_name from all_external_tables where owner = upper('&&schema'))
    and C.data_type in ('VARCHAR2', 'CHAR')
  -- You can exclude or include tables or schemas as you wish, by adjusting
  -- "and T.owner not in" as per your requirements
/
===========
@cursor.sql
===========
declare
  cursor c1 is select * from semantics$;
  v_statement varchar2(255);
  v_nc number(10);
  v_nt number(10);
begin
  execute immediate
    'select count(*) from semantics$' into v_nc;
  execute immediate
    'select count(distinct s_table_name) from semantics$' into v_nt;
  dbms_output.put_line
    ('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
  for r1 in c1 loop
    v_statement := 'ALTER TABLE ' || r1.s_owner || '.' || r1.s_table_name;
    v_statement := v_statement || ' modify (' || r1.s_column_name || ' ';
    v_statement := v_statement || r1.s_data_type || '(' || r1.s_char_length;
    v_statement := v_statement || ' CHAR))';
    --dbms_output.put_line(v_statement);
    execute immediate v_statement;
  end loop;
  dbms_output.put_line('Done');
end;
/
=========================================
Please consider the tablespaces on production and remap them in testing.