This discussion is archived
1 Reply Latest reply: May 27, 2013 1:13 AM by sybrand_b RSS

create table with NLS_NCHAR_CHARACTERSET AL16UTF16

609887 Newbie
Currently Being Moderated
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
    sybrand_b Guru
    Currently Being Moderated
    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


  • Correct Answers - 10 points
  • Helpful Answers - 5 points