Transportable Tablespaces – Characters Sets – Same same but different?

All credits go to Don Wolf, an Oracle Advanced Customer Support engineer from Ohio as he dug out this information 🙂 Thanks Don!

Do database character sets have to match EXACTLY for Transportable Tablespaces?

That sounds like a simple question. When you look into our big slide deck the answer will be a straight “Yes”. No doubts. Regardless if you would like to do Transportable Tablespaces or Full Transportable Export/Import your sources and your target’s database character sets must be equal. Otherwise Data Pump won’t allow you to process the meta data import.

But Don was wondering about slightly differing information in MOS notes and the documentation.
And asked me if I can clarify things. I couldn’t.

• MOS Note:1454872.1
  Transportable Tablespace (TTS) Restrictions and Limitations:
  Details, Reference, and Version Where Applicable
  tells you:
  • Character Set and National Character Set Limitation
    • Restriction :  The source and target database must use a compatible character set and national character set.
    • Reference  : Oracle Database Administrators Guide – Transporting Tablespaces Between Databases – Limitations on Transportable Tablespace Use
    • Affected Version :  All version
•  And the above documentation link then tells you:
  • The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
    • The database character sets of the source and the target databases are the same.
    • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
      • The source database is in version 10.1.0.3 or higher.
      • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
      • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
    • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
      • The source database is in a version lower than 10.1.0.3.
      • The maximum character width is the same in the source and target database character sets.
• And furthermore from “Choosing a Character Set” section of Oracle 12.1  Database Globalization Support Guide:
  • Oracle
    Database does not maintain a list of all subset-superset pairs but it
    does maintain a list of binary subset-superset pairs that it recognizes
    in various situations such as checking compatibility of a transportable
    tablespace or a pluggable database. Table A-11 and Table A-12 list the
    binary subset-superset pairs recognized by Oracle Database.
  • In those tables the only binary subset-superset pairs involving AL32UTF8 are:
    • UTF8/AL32UTF8
    • US7ASCII/AL32UTF8
      
• This is not 100% congruent to the information provided in
  MOS Note 371556.1 – How to Migrate to different Endian Platform Using
  Transportable Tablespaces With RMAN
  saying:
  “The source and target database must use the same character set and national character set.”
  

What is correct, what’s not?

First of all the wording of “compatible character sets” seems to be gotten introduced with Oracle Database 11.2.

In fact the scope for the target system has become broader in Oracle Database 11.2.  These rule here are correct as soon as your target database is an Oracle Database 11.2 or newer database release.

• The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
  • The database character sets of the source and the target databases are the same.
  • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
    • The source database is in version 10.1.0.3 or higher.
    • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
    • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
  • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
    • The source database is in a version lower than 10.1.0.3.
    • The maximum character width is the same in the source and target database character sets.
      

–Mike