Oracle Database 11g Enterprise Edition Release 11.2
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?
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.
Senior Oracle DBA