oracle 10g常见问题精选 (2)

来源：love 作者：winter等出处：巧巧读书 2005-11-01 进入讨论组

访问 http://www.qqread.com/oracle/h125294010.html

三、用dblink在10G中做create table ..as select * from ..varchar2()的column宽度变成3倍
　　问题描述：例如：varchar2(255)==>varchar(765)

　　测试：1. 环境: 两台机器都装的10G的数据库
　　2. 测试步骤和结果:

SQL> conn test1/test1@db10G_server1
SQL> create database link link_server2

connect to test1 identified by test1

using 'db10G_server2'
SQL> conn test1/test1@db10G_server2
SQL> create table test1_t1 as select * from all_objects

where rownum<=3000;
SQL> desc test1_t1;
Name Null? Type

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(30)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(19)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)
SQL> conn test1/test1@db10G_server1
SQL> create table test1_linkt1 as

select * from test1_t1@link_server2;
SQL> desc test1_linkt1
Name Null? Type

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

OWNER VARCHAR2(60)

OBJECT_NAME VARCHAR2(60)

SUBOBJECT_NAME VARCHAR2(60)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(38)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(38)

STATUS VARCHAR2(14)

TEMPORARY VARCHAR2(2)

GENERATED VARCHAR2(2)

SECONDARY VARCHAR2(2)

　　表结构一样，但是列的长度确实变了, 通过dblink创建的表的varchar2列的长度是原表的两倍

　　再来看看字符集

　　1.Server1上的数据库字符集

SQL> column value format A20
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

value
--------------
ZHS16GBK

　　2. Server2上的数据库字符集

SQL> conn test1/test1@db10G_server2
SQL> column value format A20
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

value
--------------
AL32UTF8

　　结论: 与数据库字符集不同有关系.