首页 » ORACLE 9i-23ai » ORA-20011 ORA-12801 ORA-01722 when gather table statistics
ORA-20011 ORA-12801 ORA-01722 when gather table statistics
上周在优化统计信息的收集策略时，在做一个普通的分区表（HEAP TABLE）收集统计信息时进程报错意外终止，后台日志未出现ora-600等内部错误，这是一套11.2.0.3.7 oracle RAC 2-nodes on HPUX-ia 11.31的运行环境，这里简单的记录一下排查过程。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ANBOB' , tabname => 'TAB_ERROR' , cascade => true, estimate_percent => dbms_stats.auto_sample_size,method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', degree => 8,no_invalidate=>false); * ERROR at line 1: ORA-20011: Approximate NDV failed: ORA-12801: error signaled in parallel query server P001, instance qdim1:im1 (1) ORA-01722: invalid number ORA-06512: at "SYS.DBMS_STATS", line 23836 ORA-06512: at "SYS.DBMS_STATS", line 23887 ORA-06512: at line 1
Note:
这个错误开始出现的有parallel相关的错误，通常我会逐渐的简化SQL到最后定位问题根源，先取掉并行再试。
SQL> BEGIN dbms_stats.gather_table_stats('ANBOB','TAB_ERROR',method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END;
2 /
BEGIN dbms_stats.gather_table_stats('IM','TAB_ERROR',method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false); END;
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_STATS", line 23836
ORA-06512: at "SYS.DBMS_STATS", line 23887
ORA-06512: at line 1
Note:
基本可以排除parallel引起的错误, “NDV” 通常是统计信息中的唯一值(Number of Distinct Value), 错误与ora-1722有关， 在MOS中未找到任何相似的BUG或案例, 下面是表的创建语句。
CREATE TABLE "ANBOB"."TAB_ERROR"
( "REGION" NUMBER(6,0) NOT NULL ENABLE,
"INV_ID" VARCHAR2(32) NOT NULL ENABLE,
"RES_TYPE_ID" VARCHAR2(32),
"INV_STATUS" VARCHAR2(32) NOT NULL ENABLE,
"STATUS_DATE" DATE,
"STATUS_REASON" VARCHAR2(32),
"LDSTORE_BUSI_ID" VARCHAR2(32),
"LAST_BUSI_ID" VARCHAR2(32) NOT NULL ENABLE,
"STORE_ID" VARCHAR2(32),
"ORG_ID" VARCHAR2(32),
"PHYSICAL_STATUS" VARCHAR2(32) NOT NULL ENABLE,
"BUSI_STATUS" VARCHAR2(32) NOT NULL ENABLE,
"HLR_STATUS" VARCHAR2(32),
"HLR_ID" VARCHAR2(32) DEFAULT 'HLR001',
"GROUP_ID" VARCHAR2(32),
"USE_CNT" NUMBER(6,0) DEFAULT 0,
"PRE_PRICE" NUMBER(8,0) DEFAULT 0,
"BUNDLE_IMSI" VARCHAR2(32),
"OPER_ID" VARCHAR2(32),
"USE_ORG_ID" VARCHAR2(32),
"BRAND_ID" VARCHAR2(32),
"PRODUCT_ID" VARCHAR2(256),
"TELNUM_GRADE" VARCHAR2(32),
"CREATE_DATE" DATE,
"MEMO" VARCHAR2(500),
"USER_PASSWORD" VARCHAR2(32),
"ADDDATA1" VARCHAR2(32),
"ADDDATA2" VARCHAR2(32),
"RECYCLE_DATE" DATE,
"LDSTORE_DATE" DATE DEFAULT SYSDATE,
"TELNUM_CLASS" VARCHAR2(32) DEFAULT '0' NOT NULL ENABLE,
"LOW_CONSUM_FEE" NUMBER(32,0) DEFAULT 0,
"LOW_CONSUM_PRE" NUMBER(32,0) DEFAULT 0,
"LOW_INSERVICE_TIME" NUMBER(10,0) DEFAULT 0,
"TELNUMTAIL8" VARCHAR2(32),
"TELNUMTAIL7" VARCHAR2(32),
"TELNUMTAIL6" VARCHAR2(32),
"TELNUMTAIL5" VARCHAR2(32),
"TELNUMTAIL4" VARCHAR2(32),
"TELNUMTAIL3" VARCHAR2(32),
"TELNUM_CLASSNAME" VARCHAR2(32),
"MATCH_PATTERN" VARCHAR2(32),
"NEXT_CALCGRADE_DATE" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 40 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA999"
PARTITION BY RANGE ("REGION")
(PARTITION "PART_310" VALUES LESS THAN (311) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 40 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA310" ,
PARTITION "PART_311" VALUES LESS THAN (312) SEGMENT CREATION IMMEDIATE
...
使用10046 or errorstack 都是可以的， 这里trace一下出错信息.
SQL> alter session set events '10046 trace name context forever, level 12：1722 trace name errorstack level 3';
Session altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'ANBOB' , tabname => 'TAB_ERROR' ,
cascade => true, estimate_percent => dbms_stats.auto_sample_size,
method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', no_invalidate=>false);
*
ERROR at line 1:
ORA-20011: Approximate NDV failed: ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_STATS", line 23836
ORA-06512: at "SYS.DBMS_STATS", line 23887
ORA-06512: at line 1
-- == 10046 trace
WAIT #11529215043894653336: nam='library cache pin' ela= 337 handle address=13835058131968295736 pin address=13835058130966052184 100*mode+namespace=25589415280642 obj#=15388 tim=37250183459212
LOBGETLEN: c=0,e=6,p=0,cr=0,cu=0,tim=37250183460205
LOBREAD: c=0,e=31,p=0,cr=1,cu=0,tim=37250183460276
LOBREAD: c=0,e=13,p=0,cr=1,cu=0,tim=37250183460318
LOBGETLEN: c=0,e=3,p=0,cr=0,cu=0,tim=37250183460407
LOBREAD: c=0,e=12,p=0,cr=1,cu=0,tim=37250183460447
LOBREAD: c=0,e=11,p=0,cr=1,cu=0,tim=37250183460485
LOBGETLEN: c=0,e=5,p=0,cr=0,cu=0,tim=37250183461458
LOBREAD: c=0,e=21,p=0,cr=1,cu=0,tim=37250183461518
=====================
PARSING IN CURSOR #11529215043892965104 len=7279 dep=1 uid=32 oct=3 lid=0 tim=37250183466741 hv=988474521 ad='c0000011fa02b6c8' sqlid='68kd0nhxfpv4t'
:q
-- == errorstack trace
*** 2016-04-05 17:46:21.679
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-01722: invalid number
----- Current SQL Statement for this session (sql_id=68kd0nhxfpv4t) -----
SQL> select sql_fulltext from v$sqlarea where sql_id='&sqlid';
Enter value for sqlid: 68kd0nhxfpv4t
SQL_FULLTEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
/* SQL Analyze(0) */ select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("LOW_INSERVICE_TIME")),to_char(substrb(dump(min("LOW_INSERVICE_TIME"),16,0,32),1,120)),to_char(substrb(dump(max("LOW_INSERVICE_TIME"),16,0,32),1,120)),to_char(count("TELNUMTAIL8")),to_char(substrb(dump(min("TELNUMTAIL8"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL8"),16,0,32),1,120)),to_char(count("TELNUMTAIL7")),to_char(substrb(dump(min("TELNUMTAIL7"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL7"),16,0,32),1,120)),to_char(count("TELNUMTAIL6")),to_char(substrb(dump(min("TELNUMTAIL6"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL6"),16,0,32),1,120)),to_char(count("TELNUMTAIL5")),to_char(substrb(dump(min("TELNUMTAIL5"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL5"),16,0,32),1,120)),to_char(count("TELNUMTAIL4")),to_char(substrb(dump(min("TELNUMTAIL4"),16,0,
32),1,120)),to_char(substrb(dump(max("TELNUMTAIL4"),16,0,32),1,120)),to_char(count("TELNUMTAIL3")),to_char(substrb(dump(min("TELNUMTAIL3"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUMTAIL3"),16,0,32),1,120)),to_char(count("TELNUM_CLASSNAME")),to_char(substrb(dump(min("TELNUM_CLASSNAME"),16,0,32),1,120)),to_char(substrb(dump(max("TELNUM_CLASSNAME"),16,0,32),1,120)),to_char(count("MATCH_PATTERN")),to_char(substrb(dump(min("MATCH_PATTERN"),16,0,32),1,120)),to_char(substrb(dump(max("MATCH_PATTERN"),16,0,32),1,120)),to_char(count("NEXT_CALCGRADE_DATE")),to_char(substrb(dump(min("NEXT_CALCGRADE_DATE"),16,0,32),1,120)),to_char(substrb(dump(max("NEXT_CALCGRADE_DATE"),16,0,32),1,120)),to_char(count("SYS_NC00044$")),to_char(substrb(dump(min("SYS_NC00044$"),16,0,32),1,120)),to_char(substrb(dump(max("SYS_NC00044$"),16,0,32),1,120)),to_char(count("SYS_NC00045$")),to_char(substrb(dump(min("SYS_NC00045$"),16,0,32),1,120)),to_char(substrb(dump(max("SYS_NC00045$"),16,0,32),1,120)),to_char(count("
BRAND_ID")),to_char(substrb(dump(min("BRAND_ID"),16,0,32),1,120)),to_char(substrb(dump(max("BRAND_ID"),16,0,32),1,120)),to_char(count("PRODUCT_ID")),to_char(substrb(dump(min(
...
from "ANBOB"."TAB_ERROR" t where TBL$OR$IDX$PART$NUM("ANBOB"."TAB_ERROR",0,4,0,"ROWID") = :objn /*
SQL> select column_name,column_id,hidden_column,virtual_column,segment_column_id,internal_column_id
2 from dba_tab_cols where owner='ANBOB' and table_name='TAB_ERROR' ORDER BY 2;
COLUMN_NAME COLUMN_ID HIDDEN VIRTUA SEGMENT_COLUMN_ID INTERNAL_COLUMN_ID
------------------------- ---------- ------ ------ ----------------- ------------------
REGION 1 NO NO 1 1
INV_ID 2 NO NO 2 2
RES_TYPE_ID 3 NO NO 3 3
INV_STATUS 4 NO NO 4 4
STATUS_DATE 5 NO NO 5 5
STATUS_REASON 6 NO NO 6 6
LDSTORE_BUSI_ID 7 NO NO 7 7
LAST_BUSI_ID 8 NO NO 8 8
STORE_ID 9 NO NO 9 9
ORG_ID 10 NO NO 10 10
PHYSICAL_STATUS 11 NO NO 11 11
BUSI_STATUS 12 NO NO 12 12
HLR_STATUS 13 NO NO 13 13
HLR_ID 14 NO NO 14 14
...
TELNUM_CLASS 31 NO NO 31 31
LOW_CONSUM_FEE 32 NO NO 32 32
LOW_CONSUM_PRE 33 NO NO 33 33
LOW_INSERVICE_TIME 34 NO NO 34 34
TELNUMTAIL8 35 NO NO 35 35
TELNUMTAIL7 36 NO NO 36 36
TELNUMTAIL6 37 NO NO 37 37
TELNUMTAIL5 38 NO NO 38 38
TELNUMTAIL4 39 NO NO 39 39
TELNUMTAIL3 40 NO NO 40 40
TELNUM_CLASSNAME 41 NO NO 41 41
MATCH_PATTERN 42 NO NO 42 42
NEXT_CALCGRADE_DATE 43 NO NO 43 43
SYS_NC00044$ YES YES 44
SYS_NC00045$ YES YES 45
SQL> select col#,name,TYPE#,DEFAULT$ ,property from col$ where OBJ#=15386
order by 1
COL# NAME TYPE# DEFAULT$ PROPERTY
---------- ---------------------- ---------- ----------------------------------- ----------
0 SYS_NC00045$ 2 "PRE_PRICE"+NVL("LOW_CONSUM_PRE",0) 65832
0 SYS_NC00044$ 2 TO_NUMBER("TELNUM_CLASS") 327976
1 REGION 2 0
2 INV_ID 1 0
3 RES_TYPE_ID 1 0
4 INV_STATUS 1 0
5 STATUS_DATE 12 0
6 STATUS_REASON 1 0
7 LDSTORE_BUSI_ID 1 0
8 LAST_BUSI_ID 1 0
9 STORE_ID 1 0
10 ORG_ID 1 0
11 PHYSICAL_STATUS 1 0
12 BUSI_STATUS 1 0
13 HLR_STATUS 1 0
14 HLR_ID 1 'HLR001' 0
15 GROUP_ID 1 0
16 USE_CNT 2 0 0
17 PRE_PRICE 2 0 0
18 BUNDLE_IMSI 1 0
19 OPER_ID 1 0
20 USE_ORG_ID 1 0
21 BRAND_ID 1 0
22 PRODUCT_ID 1 0
23 TELNUM_GRADE 1 0
24 CREATE_DATE 12 0
25 MEMO 1 0
26 USER_PASSWORD 1 0
27 ADDDATA1 1 0
28 ADDDATA2 1 0
29 RECYCLE_DATE 12 0
30 LDSTORE_DATE 12 SYSDATE 0
31 TELNUM_CLASS 1 '0' 0
32 LOW_CONSUM_FEE 2 0 0
33 LOW_CONSUM_PRE 2 0 0
34 LOW_INSERVICE_TIME 2 0 0
35 TELNUMTAIL8 1 0
36 TELNUMTAIL7 1 0
37 TELNUMTAIL6 1 0
38 TELNUMTAIL5 1 0
39 TELNUMTAIL4 1 0
40 TELNUMTAIL3 1 0
41 TELNUM_CLASSNAME 1 0
42 MATCH_PATTERN 1 0
43 NEXT_CALCGRADE_DATE 12 0
45 rows selected.
TIP:
property number not null, /* column properties (bit flags): */
/* 0x0001 = 1 = ADT attribute column */
/* 0x0002 = 2 = OID column */
/* 0x0004 = 4 = nested table column */
/* 0x0008 = 8 = virtual column */
/* 0x0010 = 16 = nested table's SETID$ column */
/* 0x0020 = 32 = hidden column */
/* 0x0040 = 64 = primary-key based OID column */
/* 0x0080 = 128 = column is stored in a lob */
/* 0x0100 = 256 = system-generated column */ ---
/* 0x0200 = 512 = rowinfo column of typed table/view */
/* 0x0400 = 1024 = nested table columns setid */
/* 0x0800 = 2048 = column not insertable */
/* 0x1000 = 4096 = column not updatable */
/* 0x2000 = 8192 = column not deletable */
/* 0x4000 = 16384 = dropped column */
/* 0x8000 = 32768 = unused column - data still in row */
/* 0x00010000 = 65536 = virtual column */ ---
/* 0x00020000 = 131072 = place DESCEND operator on top */
/* 0x00040000 = 262144 = virtual column is NLS dependent */
/* 0x00080000 = 524288 = ref column (present as oid col) */
/* 0x00100000 = 1048576 = hidden snapshot base table column */
/* 0x00200000 = 2097152 = attribute column of a user-defined ref */
/* 0x00400000 = 4194304 = export hidden column,RLS on hidden col */
/* 0x00800000 = 8388608 = string column measured in characters */
/* 0x01000000 = 16777216 = virtual column expression specified */
/* 0x02000000 = 33554432 = typeid column */
/* 0x04000000 = 67108864 = Column is encrypted */
/* 0x20000000 = 536870912 = Column is encrypted without salt */
65832=65536 + 256 + 32 + 8
327976 = 262144 + 65536 + 256 + 32 + 8
Note:
SQL中含有该表上两个系统生成的SYS_NC开头的列， SYS_NC开头的通常是创建表上的函数索引时自动增加的虚拟列。
SQL> select INDEX_NAME,INDEX_TYPE from dba_indexes where TABLE_NAME='TAB_ERROR' AND TABLE_OWNER='ANBOB';
INDEX_NAME INDEX_TYPE
---------------------------------------- ------------------------
IDX_IMTELNUMIMSI NORMAL
IDX_IMTELNUMTYPE NORMAL
IDX_IMTELNUM_INV_ORG NORMAL
IDX_IMTELNUM_ORG_INV NORMAL
PK_IMINVTELNUM NORMAL
IDX_IMINVTELNUM_LASTBUSIID NORMAL
IDX_IMINVTELNUM_LDBUSIID NORMAL
IDX_IMTELNUMTAIL8 NORMAL
IDX_IMTELNUMTAIL7 NORMAL
IDX_IMTELNUMTAIL6 NORMAL
IDX_IMTELNUMTAIL5 NORMAL
IDX_IMTELNUMTAIL4 NORMAL
IDX_IMTELNUMTAIL3 NORMAL
IDX_IMTELNUMORGID NORMAL
IDX_IMTELNUMINVIDORGID NORMAL
SQL> explain plan for
2 select * from ANBOB.TAB_ERROR where "PRE_PRICE"+NVL("LOW_CONSUM_PRE",0)=10;
Explained.
SQL> @x2
PLAN_TABLE_OUTPUT
------------------------------------------
Plan hash value: 1631671213
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500K| 117M| 576K (1)| 01:55:18 | | |
| 1 | PARTITION RANGE ALL| | 500K| 117M| 576K (1)| 01:55:18 | 1 | 12 |
|* 2 | TABLE ACCESS FULL | TAB_ERROR | 500K| 117M| 576K (1)| 01:55:18 | 1 | 12 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PRE_PRICE"+NVL("LOW_CONSUM_PRE",0)=10)
14 rows selected.
Note:
如果列上有函数索引，根据列上的DEFAULT 值，上面的SQL 应该可以使用函数索引，但是还是全表扫描。确认了表上并未包含函数索引。 如果这样的话就说明有数据字典不一致的情况， 可以使用Hcheck.sql 进行扫描，该脚本MOS上有提供。
SQL> @hcheck3
Package created.
Package body created.
H.Check Version 9i+/hc3.50
---------------------------------------
Catalog Version 11.2.0.3.0 (1102000300)
---------------------------------------
Catalog Fixed
Procedure Name Version Vs Release Run
------------------------------ ... ---------- -- ---------- ---
.- SynLastDDLTim ... 1102000300 > 1001000200 : n/a
.- LobNotInObj ... 1102000300 > 1000000200 : n/a
.- MissingOIDOnObjCol ... 1102000300 <= *All Rel* : Ok .- SourceNotInObj ... 1102000300 > 1002000100 : n/a
.- IndIndparMismatch ... 1102000300 > 1102000100 : n/a
.- InvCorrAudit ... 1102000300 > 1102000100 : n/a
.- OversizedFiles ... 1102000300 <= *All Rel* : Ok .- TinyFiles ... 1102000300 > 900010000 : n/a
.- PoorDefaultStorage ... 1102000300 <= *All Rel* : Ok
.- PoorStorage ... 1102000300 <= *All Rel* : Ok .- MissTabSubPart ... 1102000300 > 900010000 : n/a
.- PartSubPartMismatch ... 1102000300 > 1102000100 : n/a
.- TabPartCountMismatch ... 1102000300 <= *All Rel* : Ok .- OrphanedTabComPart ... 1102000300 > 900010000 : n/a
.- ZeroTabSubPart ... 1102000300 > 902000100 : n/a
.- MissingSum$ ... 1102000300 <= *All Rel* : Ok
.- MissingDir$ ... 1102000300 <= *All Rel* : Ok
.- DuplicateDataobj ... 1102000300 <= *All Rel* : Ok
.- ObjSynMissing ... 1102000300 <= *All Rel* : Ok
.- ObjSeqMissing ... 1102000300 <= *All Rel* : Ok
.- OrphanedUndo ... 1102000300 <= *All Rel* : Ok
.- OrphanedIndex ... 1102000300 <= *All Rel* : Ok
.- OrphanedIndexPartition ... 1102000300 <= *All Rel* : Ok
.- OrphanedIndexSubPartition ... 1102000300 <= *All Rel* : Ok
.- OrphanedTable ... 1102000300 <= *All Rel* : Ok
.- OrphanedTablePartition ... 1102000300 <= *All Rel* : Ok
.- OrphanedTableSubPartition ... 1102000300 <= *All Rel* : Ok
.- MissingPartCol ... 1102000300 <= *All Rel* : Ok
.- OrphanedSeg$ ... 1102000300 <= *All Rel* : Ok .- OrphanedIndPartObj# ... 1102000300 > 1101000600 : n/a
.- DuplicateBlockUse ... 1102000300 <= *All Rel* : Ok .- HighObjectIds ... 1102000300 > 801060000 : n/a
.- PQsequence ... 1102000300 > 800060000 : n/a
.- TruncatedCluster ... 1102000300 > 801070000 : n/a
.- FetUet ... 1102000300 <= *All Rel* : Ok
.- Uet0Check ... 1102000300 <= *All Rel* : Ok
.- ExtentlessSeg ... 1102000300 <= *All Rel* : Ok
.- SeglessUET ... 1102000300 <= *All Rel* : Ok
.- BadInd$ ... 1102000300 <= *All Rel* : Ok
.- BadTab$ ... 1102000300 <= *All Rel* : Ok .- BadIcolDepCnt ... 1102000300 > 1101000700 : n/a
.- WarnIcolDep ... 1102000300 > 1101000700 : n/a
.- OnlineRebuild$ ... 1102000300 <= *All Rel* : Ok .- DropForceType ... 1102000300 > 1001000200 : n/a
.- TrgAfterUpgrade ... 1102000300 <= *All Rel* : Ok
.- FailedInitJVMRun ... 1102000300 <= *All Rel* : Ok .- TypeReusedAfterDrop ... 1102000300 > 900010000 : n/a
.- Idgen1$TTS ... 1102000300 > 900010000 : n/a
.- DroppedFuncIdx ... 1102000300 > 902000100 : n/a
.- BadOwner ... 1102000300 > 900010000 : n/a
.- UpgCheckc0801070 ... 1102000300 <= *All Rel* : Ok
.- BadPublicObjects ... 1102000300 <= *All Rel* : Ok
.- BadSegFreelist ... 1102000300 <= *All Rel* : Ok .- BadCol# ... 1102000300 > 1001000200 : n/a
.- BadDepends ... 1102000300 <= *All Rel* : Ok
.- CheckDual ... 1102000300 <= *All Rel* : Ok
.- ObjectNames ... 1102000300 <= *All Rel* : Ok
.- BadCboHiLo ... 1102000300 <= *All Rel* : Ok
.- ChkIotTs ... 1102000300 <= *All Rel* : Ok
.- NoSegmentIndex ... 1102000300 <= *All Rel* : Ok
.- BadNextObject ... 1102000300 <= *All Rel* : Ok .- OrphanIndopt ... 1102000300 > 902000800 : n/a
.- UpgFlgBitTmp ... 1102000300 > 1001000100 : n/a
.- RenCharView ... 1102000300 > 1001000100 : n/a
.- Upg9iTab$ ... 1102000300 > 902000400 : n/a
.- Upg9iTsInd ... 1102000300 > 902000500 : n/a
.- Upg10gInd$ ... 1102000300 > 1002000000 : n/a
.- DroppedROTS ... 1102000300 <= *All Rel* : Ok .- ChrLenSmtcs ... 1102000300 > 1101000600 : n/a
.- FilBlkZero ... 1102000300 <= *All Rel* : Ok
.- DbmsSchemaCopy ... 1102000300 <= *All Rel* : Ok
Found 0 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output
to check if the above needs attention or not
note:
Hcheck 未明确存在错误，实际col$确实多出两列函数虚拟列的记录，在基表ind$ / obj$ / icol$ / indpart$ 中未找到对应记录。下面分析这两个虚拟列与上面的报错有什么关系？
select /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */
to_char(count("SYS_NC00044$")),to_char(substrb(dump(min("SYS_NC00044$"),16,0,32),1,120)), <<<<<<<<<<<< to_char(substrb(dump(max("SYS_NC00044$"),16,0,32),1,120)),to_char(count("SYS_NC00045$")), to_char(substrb(dump(min("SYS_NC00045$"),16,0,32),1,120)),to_char(substrb(dump(max("SYS_NC00045$"),16,0,32),1,120)) ... from "ANBOB"."TAB_ERROR" t where TBL$OR$IDX$PART$NUM("ANBOB"."TAB_ERROR",0,4,0,"ROWID") = :objn SQL> select col#,name,TYPE#,DEFAULT$ from col$ where OBJ#=15386 order by 1
COL# NAME TYPE# DEFAULT$
---------- ------------------------------ ---------- --------------------------------------------------
0 SYS_NC00045$ 2 "PRE_PRICE"+NVL("LOW_CONSUM_PRE",0)
0 SYS_NC00044$ 2 TO_NUMBER("TELNUM_CLASS") <<<<<<<<<<<<<<<<<
CREATE TABLE "ANBOB"."TAB_ERROR"
......
"PRE_PRICE" NUMBER(8,0) DEFAULT 0,
"LOW_CONSUM_PRE" NUMBER(32,0) DEFAULT 0,
"TELNUM_CLASS" VARCHAR2(32) DEFAULT '0' NOT NULL ENABLE, <<<<<<<<<<<<<<<<
......
Note:
统计信息中SQL 中用到了两个虚拟列， 虚拟列是基于表上的另外三个列，对于number列的做计算不会有问题，因为 SYS_NC00044$= to_number(telnum_class)， to_char(count(“SYS_NC00044$”)) 可以换算成to_char(count(to_number(telnum_class)))，又因为telnum_class为varchar2类型，所以如果telnum_class列存在非数值类型格式的值，就会出现ORA-01722: invalid number错误。
SQL> select to_char(count("SYS_NC00044$")) from anbob.TAB_ERROR;
select to_char(count("SYS_NC00044$")) from anbob.TAB_ERROR
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select to_char(count(to_number(telnum_class))) from anbob.TAB_ERROR;
select to_char(count(to_number(telnum_class))) from anbob.TAB_ERROR
*
ERROR at line 1:
ORA-01722: invalid number
create or replace function isnumber(p_in varchar2) return number as
i number;
begin
i := to_number(p_in);
return 1;
exception
when others then
return 0;
end;
SQL> select rowid rid,telnum_class from anbob.TAB_ERROR where isnumber(telnum_class)=0;
RID TELNUM_CLASS
------------------ ----------------------------------------------------------------
AACjsZABrAACN/gAAQ 2008-6-21 5:49:54
AACjsZABrAACN/gAAR 2008-9-28 14:56:56
AACjsZABrAACN/gAAY 2009-4-13 9:44:39
AACjsZABtAAAnhxAAC 2009-5-12 9:23:40
AACjsZABtAAAnhxAAP 2009-5-12 9:23:45
AACjsZABuAAB5l9AAT 2008-6-21 5:54:14
AACjsZABuAAB6aCAAB 2008-6-21 5:58:04
...
NOTE:
找出telnum_class列中非数字格式的值, 首先可以创建一个简单的函数。到这里整个问题就清晰了，因为数据字典中列上存在两个虚拟列，其中SYS_NC00044$虚拟列是基于telnum_class字符列的to_number， telnum_class上存在非数字的值，在收集列上的统计信息时如NDV报ora-1722错误。
解决方法：
1， 常规保守型
1) Create table as select * from ....; 2) Recreate the indexes on the new table 3) Run dbms_stats on the new table to see if it works 4) Drop the old table and rename the new table OR using Exchange partition to save time 1) Create a new partitioned table with the same structure with the old table. 2) Create a temp table (non-partitioned) with the same structure with the old table: 3) Exchange the old table's partition (anbob.TAB_ERROR) with the temp table(anbob.tmp): 4) Exchange the new table s partition(anbob.TAB_ERROR_new) with the temp table(anbob.tmp): 5) Exchange all of the partitions in the old table as step 3&4. 6) Recreate the indexes on the new table. 7) Run dbms_stats on the new table to see if it works. 8) Drop the old table and rename the new table.
2， 简单粗暴型
The operation below is in test DB env of store device synchronous(Note do not test the steps below in a production database)
SQL> delete from col$ where OBJ#=15386 and col#=0; rows deleted. SQL> commit; Commit complete. SQL> alter system flush shared_pool; System altered. SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'anbob' , tabname => 'TAB_ERROR' , cascade => true, estimate_percent => dbms_stats.auto_sample_size, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', degree => 8,no_invalidate=>false); PL/SQL procedure successfully completed.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。