1 Reply Latest reply on May 27, 2013 8:13 AM by sybrand_b

    create table with NLS_NCHAR_CHARACTERSET AL16UTF16

      Oracle Database 11g Enterprise Edition Release 11.2

      Hi all,

      We facing with such an issue. When launching
      CREATE TABLE tm_table as select * from rmt_table@dblink;
      we get in tm_table all the varchar2 columns type size tripled, instead 1 Byte - 3 Byte, 20-60 etc.

      We think that is related with NLS_NCHAR_CHARACTERSET parameter which on our DB is AL16UTF16, but on remote one is UTF8 (remote DB is Oracle 10g).

      Is it true? If yes, what workarounds should we consider to keep the original size of the remote table?

      Regards Alex.
        • 1. Re: create table with NLS_NCHAR_CHARACTERSET AL16UTF16
          You have two different charactersets. The characters are automatically converted to the target characterset.
          The number of bytes per character in both charactersets is different.
          The varchar2 columns should have been set up as
          description varchar2(30 char)

          The default is BYTE

          so other than a series of alter table modify statements there is no workaround and this 'problem' is self-inflicted.

          Sybrand Bakker
          Senior Oracle DBA
          1 person found this helpful