oracle 无故增大,利用CTAS方式通过透明网关创建非ORACLE数据库的表结构导致列长度增大3倍...

标题比较长，说明一下情况，源库是个informix数据库，目标库是个ORACLE数据库，

利用透明网关将informix数据库的表结构复制到ORACLE数据库中，发现CHAR和VARCHAR2类型的列长度

增大为原来的3倍。

如下所示：

这是表test在informix中的表结构：

点击(此处)折叠或打开

validno VARCHAR 30

comcode CHAR 4

ownername VARCHAR 60

ownertype CHAR 1

identifytype CHAR 2

identifynumber CHAR 20

***code CHAR 1

postaddress VARCHAR 120

postcode CHAR 6

phonenumber VARCHAR 32

companytype CHAR 3

在ORACLE中通过CTAS方式创建后的表结构如下：

点击(此处)折叠或打开

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for Linux: Version 10.2.0.5.0 - Production

NLSRTL Version 10.2.0.5.0 - Production

SQL> create table huateng

2 as

3 select * from "test"@dg4ifmx where 1=0;

Table created.

SQL> desc huateng

Name Null? Type

----------------------------------------- -------- ----------------------------

validno NOT NULL VARCHAR2(90)

comcode NOT NULL CHAR(12)

ownername NOT NULL VARCHAR2(180)

ownertype NOT NULL CHAR(3)

identifytype NOT NULL CHAR(6)

identifynumber NOT NULL CHAR(60)

***code CHAR(3)

postaddress VARCHAR2(360)

postcode CHAR(18)

phonenumber VARCHAR2(96)

companytype CHAR(9)

可以看到列的长度都被无缘无故的增大了3倍。

查询了MOS，ORACLE在DOC [ID 374744.1] 描述了这个问题：

In certain cases, the the length of Oracle CHAR and VARCHAR columns might be different from the original DB2 columns.

This is due to character set differences that require more (or fewer) bytes in the Oracle representation

than in the original DB2 representation.

In the most common case, the Oracle character set Unicode AL32UTF8 uses 1, 2 or 3 bytes to represent one character. If the DB2 column is single-byte character set and defined as CHAR(10), it shows up through the gateway as CHAR(30). This is required because each single byte character (which only takes 1 byte) may require 1, 2 or 3 bytes to represent the character in Unicode AL32UTF8.

Mainly affected are CHARACTER (CHAR) columns as in Oracle the maximum length of a CHAR column is 2000 and those CHAR columns are padded with spaces.

Root cause is the NLS_LENGTH_SEMANTICS used by the gateway is byte oriented.

ORACLE给出了以下解决方法：

设置透明网关参数HS_KEEP_REMOTE_COLUMN_SIZE

11.2.0.1 设置为ALL

HS_KEEP_REMOTE_COLUMN_SIZE=ALL

11.2.0.2 设置为LOCAL

HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

我的透明网关版本是11.2.0.1，在透明网关初始化参数中增加参数HS_KEEP_REMOTE_COLUMN_SIZE=ALL：

点击(此处)折叠或打开

[gateway@dbserver admin]$ cat initdg4ifmx.ora

# This is a customized agent init file that contains the HS parameters

# that are needed for the Database Gateway for Informix

#

# HS init parameters

#

HS_FDS_CONNECT_INFO=20.1.32.104:50001/nm_1500_cb_ids/dg4ifmx

HS_FDS_RECOVERY_ACCOUNT=RECOVER

HS_FDS_RECOVERY_PWD=RECOVER

HS_FDS_TRACE_LEVEL=OFF

HS_LANGUAGE=AL32UTF8

HS_RPC_FETCH_SIZE=200000

HS_FDS_FETCH_ROWS=500

HS_NLS_LENGTH_SEMANTICS=CHAR

HS_KEEP_REMOTE_COLUMN_SIZE=ALL

#HS_FDS_TRACE_FILE_NAME =dg4ifmx_trace.trc

重新启动监听器。

点击(此处)折叠或打开

[oracle@dbserver admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 29-AUG-2012 16:28:41

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting /u01/app/oracle/product/db10gr2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.5.0 - Production

System parameter file is /u01/app/oracle/product/db10gr2/db_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/product/db10gr2/db_1/network/log/listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 10.2.0.5.0 - Production

Start Date 29-AUG-2012 16:28:41

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/db10gr2/db_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/product/db10gr2/db_1/network/log/listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Services Summary...

Service "PL***tProc" has 1 instance(s).

Instance "PL***tProc", status UNKNOWN, has 1 handler(s) for this service...

Service "carlpdb" has 1 instance(s).

Instance "carlpdb", status UNKNOWN, has 1 handler(s) for this service...

Service "dg4ifmx" has 1 instance(s).

Instance "dg4ifmx", status UNKNOWN, has 1 handler(s) for this service...

Service "piccdb" has 1 instance(s).

Instance "piccdb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

再次创建表问题解决。

点击(此处)折叠或打开

SQL> desc huateng

Name Null? Type

----------------------------------------- -------- ----------------------------

validno NOT NULL VARCHAR2(90)

comcode NOT NULL CHAR(12)

ownername NOT NULL VARCHAR2(180)

ownertype NOT NULL CHAR(3)

identifytype NOT NULL CHAR(6)

identifynumber NOT NULL CHAR(60)

***code CHAR(3)

postaddress VARCHAR2(360)

postcode CHAR(18)

phonenumber VARCHAR2(96)

companytype CHAR(9)

SQL> create table huateng1

2 as

3 select * from "test"@dg4ifmx where 1=0;

Table created.

SQL> desc huateng1

Name Null? Type

----------------------------------------- -------- ----------------------------

validno NOT NULL VARCHAR2(30)

comcode NOT NULL CHAR(4)

ownername NOT NULL VARCHAR2(60)

ownertype NOT NULL CHAR(1)

identifytype NOT NULL CHAR(2)

identifynumber NOT NULL CHAR(20)

***code CHAR(1)

postaddress VARCHAR2(120)

postcode CHAR(6)

phonenumber VARCHAR2(32)

companytype CHAR(3)

SQL>