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:

  • Ā 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

One thought on “Transportable Tablespaces – Characters Sets – Same same but different?

Leave a Reply

Your email address will not be published. Required fields are marked *