关于desc的一个奇怪问题及分析(r2第18天)
关于desc的一个奇怪问题及分析(r2第18天)
在平时的工作中，desc这个命令可谓短小精悍，可以很方便的查看表结构和not null的情况。 今天在生产环境中碰到一个有些奇怪的desc问题。 首先是数据迁移组说有一个表的constraint丢了。但是在master中有。 这种问题有两种可能，一种就是constraint确实丢了。 另一种可能性就是创建的constraint的问题，可以参见： not null constraint和check constriant的问题及分析 http://blog.itpub.net/23718752/viewspace-1154073/ 先来查看master中的情况，desc确实没有问题。 ------details from master
********** TABLE columns INFO *****************
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE DATA_DEFAULT
---------- ------------------------------ --------------- ----------- ---------- --------------------
1 ID NUMBER(12,0) 22 N
2 SYS_CREATION_DATE DATE 7 N
3 SYS_UPDATE_DATE DATE 7 Y
4 OPERATOR_ID NUMBER(9,0) 22 Y
5 APPLICATION_ID CHAR(6) 6 Y
6 DL_SERVICE_CODE CHAR(5) 5 Y
7 DL_UPDATE_STAMP NUMBER(4,0) 22 Y
8 TYPE VARCHAR2(30) 30 N
9 CODE VARCHAR2(60) 60 N
10 LANGUAGE VARCHAR2(30) 30 N
11 DESCRIPTION VARCHAR2(180) 180 Y
12 BE NUMBER(9,0) 22 N 0
********** CONSTRAINTS DETAILS INFO *****************
COLUMN_NAME CONSTRAINT_NAME C SEARCH_CONDITION DEFERRED DEFERRABLE RELY INDEX_NAME
-------------------- ------------------------------ - ----------------------------------- --------- -------------- ---- ------------------------------
BE AR1_GENE_BE_NN C "BE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
LANGUAGE AR1_GENE_LANGUAGE_NN C "LANGUAGE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
CODE AR1_GENE_CODE_NN C "CODE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
TYPE AR1_GENE_TYPE_NN C "TYPE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
SYS_CREATION_DATE AR1_GENE_SYS_CREATION_DATE_NN C "SYS_CREATION_DATE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
ID AR1_GENE_ID_NN C "ID" IS NOT NULL IMMEDIATE NOT DEFERRABLE
TYPE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
CODE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
LANGUAGE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
BE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
ID AR1_GENERIC_CODES_1UQ U IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_1UQ
11 rows selected.
但是在有问题的用户下查看，desc确实有问题
---from issue db account
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE DATA_DEFAULT
---------- ------------------------------ --------------- ----------- ---------- --------------------
1 ID NUMBER(12,0) 22 Y
2 SYS_CREATION_DATE DATE 7 Y
3 SYS_UPDATE_DATE DATE 7 Y
4 OPERATOR_ID NUMBER(9,0) 22 Y
5 APPLICATION_ID CHAR(6) 6 Y
6 DL_SERVICE_CODE CHAR(5) 5 Y
7 DL_UPDATE_STAMP NUMBER(4,0) 22 Y
8 TYPE VARCHAR2(30) 30 Y
9 CODE VARCHAR2(60) 60 Y
10 LANGUAGE VARCHAR2(30) 30 Y
11 DESCRIPTION VARCHAR2(180) 180 Y
12 BE NUMBER(9,0) 22 Y 0
但是奇怪的是constraint都在。
********** CONSTRAINTS DETAILS INFO *****************
COLUMN_NAME CONSTRAINT_NAME C SEARCH_CONDITION DEFERRED DEFERRABLE RELY INDEX_NAME
-------------------- ------------------------------ - ----------------------------------- --------- -------------- ---- ------------------------------
BE AR1_GENE_BE_NN C "BE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
LANGUAGE AR1_GENE_LANGUAGE_NN C "LANGUAGE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
CODE AR1_GENE_CODE_NN C "CODE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
TYPE AR1_GENE_TYPE_NN C "TYPE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
SYS_CREATION_DATE AR1_GENE_SYS_CREATION_DATE_NN C "SYS_CREATION_DATE" IS NOT NULL IMMEDIATE NOT DEFERRABLE
ID AR1_GENE_ID_NN C "ID" IS NOT NULL IMMEDIATE NOT DEFERRABLE
TYPE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
CODE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
LANGUAGE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
BE AR1_GENERIC_CODES_PK P IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_PK
ID AR1_GENERIC_CODES_1UQ U IMMEDIATE NOT DEFERRABLE AR1_GENERIC_CODES_1UQ
那就排除了constraint丢失的情况了，查看是不是not null constraint的问题。
得到了对应的ddl语句,查看constraint是没有问题的。但是可以看到有一个奇怪的地方，就是constraint是disable的。
CREATE TABLE "xxxx"
("ID" NUMBER(12, 0) CONSTRAINT "AR1_GENE_ID_NN" NOT NULL DISABLE,
"SYS_CREATION_DATE" DATE CONSTRAINT "AR1_GENE_SYS_CREATION_DATE_NN" NOT NULL DISABLE,
"SYS_UPDATE_DATE" DATE,
"OPERATOR_ID" NUMBER(9, 0),
"APPLICATION_ID" CHAR(6),
"DL_SERVICE_CODE" CHAR(5),
"DL_UPDATE_STAMP" NUMBER(4, 0),
"TYPE" VARCHAR2(30) CONSTRAINT "AR1_GENE_TYPE_NN" NOT NULL DISABLE,
"CODE" VARCHAR2(60) CONSTRAINT "AR1_GENE_CODE_NN" NOT NULL DISABLE,
"LANGUAGE" VARCHAR2(30) CONSTRAINT "AR1_GENE_LANGUAGE_NN" NOT NULL DISABLE,
"DESCRIPTION" VARCHAR2(180),
"BE" NUMBER(9, 0) CONSTRAINT "AR1_GENE_BE_NN" NOT NULL DISABLE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "DATAS01" LOGGING NOCOMPRESS
/
查看这个用户下constraint不为ENABLED的情况，这样可以查看还有没有类似的问题存在。但是如下的查询有些矛盾。很是蹊跷。
SQL> select constraint_name,status from user_constraints where status!='ENABLED';
no rows selected
查看有问题的表对应的constraint,查看constraint的状态，显示确实enabled.
SQL> select constraint_name,status from user_constraints where table_name='AR1_GENERIC_CODES';
CONSTRAINT_NAME STATUS
------------------------------ --------
AR1_GENE_ID_NN ENABLED
AR1_GENE_SYS_CREATION_DATE_NN ENABLED
AR1_GENE_TYPE_NN ENABLED
AR1_GENE_CODE_NN ENABLED
AR1_GENE_LANGUAGE_NN ENABLED
AR1_GENE_BE_NN ENABLED
AR1_GENERIC_CODES_PK ENABLED
AR1_GENERIC_CODES_1UQ ENABLED
所以，从以上的排查，发现这是一个数据字典中的信息不匹配造成的，应该是oracle的一个bug,查看metalink没有找到相关的bug id.
但是问题不管怎么样都得解决，如果这是一个bug,就不能直接在生产中修改，万一出现严重问题就得不偿失了。
我采用的步骤如下：
从生产的备份库中导出这个表的dump，然后导入另外一个测试环境中，可以看到在测试环境这个问题可以复现。
修复之前使用desc查看。
SQL> desc ar1_generic_codes
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(12)
SYS_CREATION_DATE DATE
SYS_UPDATE_DATE DATE
OPERATOR_ID NUMBER(9)
APPLICATION_ID CHAR(6)
DL_SERVICE_CODE CHAR(5)
DL_UPDATE_STAMP NUMBER(4)
TYPE NOT NULL VARCHAR2(30)
CODE NOT NULL VARCHAR2(60)
LANGUAGE NOT NULL VARCHAR2(30)
DESCRIPTION VARCHAR2(180)
BE NOT NULL NUMBER(9)
SQL> alter table ar1_generic_codes enable constraint AR1_GENE_ID_NN;
Table altered.
修复后，使用desc再次查看。
SQL> desc ar1_generic_codes
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(12)
SYS_CREATION_DATE DATE
SYS_UPDATE_DATE DATE
OPERATOR_ID NUMBER(9)
APPLICATION_ID CHAR(6)
DL_SERVICE_CODE CHAR(5)
DL_UPDATE_STAMP NUMBER(4)
TYPE NOT NULL VARCHAR2(30)
CODE NOT NULL VARCHAR2(60)
LANGUAGE NOT NULL VARCHAR2(30)
DESCRIPTION VARCHAR2(180)
BE NOT NULL NUMBER(9)
如果再进一步查看这个问题，很有可能是在做goldengate同步的时候引入的。这个表在使用goldengate同步后没有做任何的结构变化。
但是要验证，需要得到更多的细节来验证。