在新安装的oracle10做mview时,所有涉及varchar2的字段长度都翻番,怎么回事,新手请教?
create materialized view MV_ACC_TRANS_NOTIFY
refresh force on demand with rowid
as
select * from acc_trans_notify@ossdb
where region_id='L';
oracle10上看的
SQL> desc MV_ACC_TRANS_NOTIFY;
Name Type Nullable Default Comments
---------------- ------------ -------- ------- --------
TRANS_ID NUMBER(15)
SUBSCRIPTION_ID NUMBER(9)
SVC_TYPE NUMBER(4)
ACCOUNT_ID NUMBER(9)
REGION_ID CHAR(2)
SUBJECT_ID NUMBER(9)
START_RULE_ID NUMBER(2)
START_VALUE VARCHAR2(20)
AMOUNT_RULE_ID NUMBER(2)
AMOUNT_VALUE VARCHAR2(20)
STATUS NUMBER(1)
CREATE_TIME DATE Y
EFFECT_DATE DATE
EXPIRE_DATE DATE
EFFECT_DAYS NUMBER(4)
LAST_TRANS_MONTH CHAR(12)
TRANS_TYPE NUMBER(2)
OP_REGION_ID CHAR(2)
OPERATOR_ID VARCHAR2(24)
OP_COUNTY_ID CHAR(6)
OFFICE_ID CHAR(14)
PAY_CHANNEL NUMBER(2)
BUSINESS_CODE NUMBER(8) 这个是oracle9上的
SQL> desc ACC_TRANS_NOTIFY;
Name Type Nullable Default Comments
---------------- ------------ -------- ------- --------
TRANS_ID NUMBER(15)
SUBSCRIPTION_ID NUMBER(9)
SVC_TYPE NUMBER(4)
ACCOUNT_ID NUMBER(9)
REGION_ID CHAR(1)
SUBJECT_ID NUMBER(9)
START_RULE_ID NUMBER(2)
START_VALUE VARCHAR2(10)
AMOUNT_RULE_ID NUMBER(2)
AMOUNT_VALUE VARCHAR2(10)
STATUS NUMBER(1)
CREATE_TIME DATE Y
EFFECT_DATE DATE
EXPIRE_DATE DATE
EFFECT_DAYS NUMBER(4)
LAST_TRANS_MONTH CHAR(6)
TRANS_TYPE NUMBER(2)
OP_REGION_ID CHAR(1)
OPERATOR_ID VARCHAR2(12)
OP_COUNTY_ID CHAR(3)
OFFICE_ID CHAR(7)
PAY_CHANNEL NUMBER(2)
BUSINESS_CODE NUMBER(8)
oracle10版本
SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Productiondblink的数据库是oracle9的
SQL> select * from v$version
2 ;BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
create materialized view MV_ACC_TRANS_NOTIFY
refresh force on demand with rowid
as
select * from acc_trans_notify@ossdb
where region_id='L';
oracle10上看的
SQL> desc MV_ACC_TRANS_NOTIFY;
Name Type Nullable Default Comments
---------------- ------------ -------- ------- --------
TRANS_ID NUMBER(15)
SUBSCRIPTION_ID NUMBER(9)
SVC_TYPE NUMBER(4)
ACCOUNT_ID NUMBER(9)
REGION_ID CHAR(2)
SUBJECT_ID NUMBER(9)
START_RULE_ID NUMBER(2)
START_VALUE VARCHAR2(20)
AMOUNT_RULE_ID NUMBER(2)
AMOUNT_VALUE VARCHAR2(20)
STATUS NUMBER(1)
CREATE_TIME DATE Y
EFFECT_DATE DATE
EXPIRE_DATE DATE
EFFECT_DAYS NUMBER(4)
LAST_TRANS_MONTH CHAR(12)
TRANS_TYPE NUMBER(2)
OP_REGION_ID CHAR(2)
OPERATOR_ID VARCHAR2(24)
OP_COUNTY_ID CHAR(6)
OFFICE_ID CHAR(14)
PAY_CHANNEL NUMBER(2)
BUSINESS_CODE NUMBER(8) 这个是oracle9上的
SQL> desc ACC_TRANS_NOTIFY;
Name Type Nullable Default Comments
---------------- ------------ -------- ------- --------
TRANS_ID NUMBER(15)
SUBSCRIPTION_ID NUMBER(9)
SVC_TYPE NUMBER(4)
ACCOUNT_ID NUMBER(9)
REGION_ID CHAR(1)
SUBJECT_ID NUMBER(9)
START_RULE_ID NUMBER(2)
START_VALUE VARCHAR2(10)
AMOUNT_RULE_ID NUMBER(2)
AMOUNT_VALUE VARCHAR2(10)
STATUS NUMBER(1)
CREATE_TIME DATE Y
EFFECT_DATE DATE
EXPIRE_DATE DATE
EFFECT_DAYS NUMBER(4)
LAST_TRANS_MONTH CHAR(6)
TRANS_TYPE NUMBER(2)
OP_REGION_ID CHAR(1)
OPERATOR_ID VARCHAR2(12)
OP_COUNTY_ID CHAR(3)
OFFICE_ID CHAR(7)
PAY_CHANNEL NUMBER(2)
BUSINESS_CODE NUMBER(8)
oracle10版本
SQL> select * from v$version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Productiondblink的数据库是oracle9的
SQL> select * from v$version
2 ;BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
9I的varchar类型的到了10G了要乘以1.5倍的....
10g的zhs16gbk可以修改的,但网上很多文章不建议,且由于数据库是新装的,就在重装时设置成ZHS16CGB231280字符集就ok了