Oracle segment_name为数字的怪象
Oracle segment_name为数字的怪象
现象描述：
RAC环境下表空间内所有段名都显示为数字：
环境为：RAC Oracle 11.1.0.6,使用ASM作为存储空间
异常前所做的操作：drop user unid cascade；
select segment_name from dba_segments where tablespace_name='UNID'
SEGMENT_NAME
---------------------------------------------------------------------------------
40.1027563
40.1039083
40.1041139
40.1050355
40.1061747
40.1070963
40.1080955
40.1082107
40.1082747
40.1100027
40.1110395
40.1111547
40.1113859
40.1123075
40.1123971
40.1124739
40.1125251
40.1133827
40.1142531
40.1143043
40.1143115
40.1151827
40.1152211
根据Oracle的文档：当oracle表空间存在正在创建对象的时候，会存在这个问题。
select segment_name,tablespace_name from dba_segments group by segment_name,tablespace_name;
SEGMENT_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
38.895707 UNID
38.917467 UNID
......
38.1093371 UNID
38.1115771 UNID
38.1137795 UNID
39.59 UNID
39.155 UNID
39.195 UNID
39.347 UNID
......
对于段名为数字的段，在ALL_TABLES等视图里也查询不到记录，如
SQL>
SQL> select OWNER,TABLE_NAME from ALL_TABLES where TABLE_NAME='38.895707' ;
no rows selected
SQL> select OWNER,OBJECT_NAME from ALL_OBJECTS where OBJECT_NAME='38.895707';
no rows selected
SQL> select OWNER,TABLE_NAME from ALL_TABLES where TABLE_NAME='39.155';
no rows selected
SQL> select OWNER,OBJECT_NAME from ALL_OBJECTS where OBJECT_NAME='39.155';
no rows selected
通过hcheck.full检查，未发现存在异常情况，检查结果hcheck.log见文末附件。
发了SR，Oracle怀疑是有用户将UNID表空间设置成了默认的临时表空间（理论上应该不存在这种可能，临时表空间需为temporary tablespace类型，
难道Oracle存在着这种BUG？即可将permanent表空间设置成temporary空间）。
但是经过检查，发现并没有用户临时表空间为UNID的情况。
最后Oracle回复可能是11.1.0.6的新BUG，但目前开发部门已经不支持该版本了。
There is no user's default temp tablespace was set to UNID. We suspect the issue is a oracle bug after research current knowledge.
Oracle 11g is desupport now, we could not open a bug for this issue on development layer.
Please keep monitor if the issue still exists if your have time to restart your database.
第一次遇到，特此纪要。
附件：
hcheck.log
SQL> execute hcheck.full
H.Check Version 9i+/hc3.50
---------------------------------------
Catalog Version 11.1.0.6.0 (1101000600)
---------------------------------------
Catalog Fixed
Procedure Name Version Vs Release Run
------------------------------ ... ---------- -- ---------- ---
.- SynLastDDLTim ... 1101000600 > 1001000200 : n/a
.- LobNotInObj ... 1101000600 > 1000000200 : n/a
.- MissingOIDOnObjCol ... 1101000600 <= *All Rel* : Ok
.- SourceNotInObj ... 1101000600 > 1002000100 : n/a
.- IndIndparMismatch ... 1101000600 <= 1102000100 : Ok
.- InvCorrAudit ... 1101000600 <= 1102000100 : Ok
.- OversizedFiles ... 1101000600 <= *All Rel* : Ok
.- TinyFiles ... 1101000600 > 900010000 : n/a
.- PoorDefaultStorage ... 1101000600 <= *All Rel* : Ok
.- PoorStorage ... 1101000600 <= *All Rel* : Ok
.- MissTabSubPart ... 1101000600 > 900010000 : n/a
.- PartSubPartMismatch ... 1101000600 <= 1102000100 : Ok
.- TabPartCountMismatch ... 1101000600 <= *All Rel* : Ok
.- OrphanedTabComPart ... 1101000600 > 900010000 : n/a
.- ZeroTabSubPart ... 1101000600 > 902000100 : n/a
.- MissingSum$ ... 1101000600 <= *All Rel* : Ok
.- MissingDir$ ... 1101000600 <= *All Rel* : Ok
.- DuplicateDataobj ... 1101000600 <= *All Rel* : Ok
.- ObjSynMissing ... 1101000600 <= *All Rel* : Ok
.- ObjSeqMissing ... 1101000600 <= *All Rel* : Ok
.- OrphanedUndo ... 1101000600 <= *All Rel* : Ok
.- OrphanedIndex ... 1101000600 <= *All Rel* : Ok
.- OrphanedIndexPartition ... 1101000600 <= *All Rel* : Ok
.- OrphanedIndexSubPartition ... 1101000600 <= *All Rel* : Ok
.- OrphanedTable ... 1101000600 <= *All Rel* : Ok
.- OrphanedTablePartition ... 1101000600 <= *All Rel* : Ok
.- OrphanedTableSubPartition ... 1101000600 <= *All Rel* : Ok
.- MissingPartCol ... 1101000600 <= *All Rel* : Ok
.- OrphanedSeg$ ... 1101000600 <= *All Rel* : Ok
.- OrphanedIndPartObj# ... 1101000600 <= 1101000600 : Ok
.- DuplicateBlockUse ... 1101000600 <= *All Rel* : Ok
.- HighObjectIds ... 1101000600 > 801060000 : n/a
.- PQsequence ... 1101000600 > 800060000 : n/a
.- TruncatedCluster ... 1101000600 > 801070000 : n/a
.- FetUet ... 1101000600 <= *All Rel* : Ok
.- Uet0Check ... 1101000600 <= *All Rel* : Ok
.- ExtentlessSeg ... 1101000600 <= *All Rel* : Ok
.- SeglessUET ... 1101000600 <= *All Rel* : Ok
.- BadInd$ ... 1101000600 <= *All Rel* : Ok
.- BadTab$ ... 1101000600 <= *All Rel* : Ok
.- BadIcolDepCnt ... 1101000600 <= 1101000700 : Ok
.- WarnIcolDep ... 1101000600 <= 1101000700 : Ok
.- OnlineRebuild$ ... 1101000600 <= *All Rel* : Ok
.- DropForceType ... 1101000600 > 1001000200 : n/a
.- TrgAfterUpgrade ... 1101000600 <= *All Rel* : Ok
.- FailedInitJVMRun ... 1101000600 <= *All Rel* : Ok
.- TypeReusedAfterDrop ... 1101000600 > 900010000 : n/a
.- Idgen1$TTS ... 1101000600 > 900010000 : n/a
.- DroppedFuncIdx ... 1101000600 > 902000100 : n/a
.- BadOwner ... 1101000600 > 900010000 : n/a
.- UpgCheckc0801070 ... 1101000600 <= *All Rel* : Ok
.- BadPublicObjects ... 1101000600 <= *All Rel* : Ok
.- BadSegFreelist ... 1101000600 <= *All Rel* : Ok
.- BadCol# ... 1101000600 > 1001000200 : n/a
.- BadDepends ... 1101000600 <= *All Rel* : Ok
HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects
[W] - P_OBJ#=61028 D_OBJ#=61081
[W] - P_OBJ#=61029 D_OBJ#=61082
[W] - P_OBJ#=61030 D_OBJ#=61083
[W] - P_OBJ#=61031 D_OBJ#=61084
[W] - P_OBJ#=61032 D_OBJ#=61085
[W] - P_OBJ#=61033 D_OBJ#=61086
[W] - P_OBJ#=61034 D_OBJ#=61087
[W] - P_OBJ#=61035 D_OBJ#=61088
[W] - P_OBJ#=61036 D_OBJ#=61089
[W] - P_OBJ#=61037 D_OBJ#=61090
[W] - P_OBJ#=61038 D_OBJ#=61091
[W] - P_OBJ#=61039 D_OBJ#=61092
[W] - P_OBJ#=61040 D_OBJ#=61093
[W] - P_OBJ#=61041 D_OBJ#=61094
[W] - P_OBJ#=61042 D_OBJ#=61095
[W] - P_OBJ#=61043 D_OBJ#=61096
[W] - P_OBJ#=61044 D_OBJ#=61097
[W] - P_OBJ#=61045 D_OBJ#=61098
[W] - P_OBJ#=61046 D_OBJ#=61099
[W] - P_OBJ#=61047 D_OBJ#=61100
[W] - P_OBJ#=61048 D_OBJ#=61101
[W] - P_OBJ#=61049 D_OBJ#=61102
[W] - P_OBJ#=61050 D_OBJ#=61103
[W] - P_OBJ#=61051 D_OBJ#=61104
[W] - P_OBJ#=61052 D_OBJ#=61105
[W] - P_OBJ#=61053 D_OBJ#=61106
[W] - P_OBJ#=61054 D_OBJ#=61107
[W] - P_OBJ#=61055 D_OBJ#=61108
[W] - P_OBJ#=61056 D_OBJ#=61109
[W] - P_OBJ#=61057 D_OBJ#=61110
[W] - P_OBJ#=61058 D_OBJ#=61111
[W] - P_OBJ#=61059 D_OBJ#=61112
[W] - P_OBJ#=61060 D_OBJ#=61113
[W] - P_OBJ#=61061 D_OBJ#=61114
[W] - P_OBJ#=61062 D_OBJ#=61115
[W] - P_OBJ#=61063 D_OBJ#=61116
[W] - P_OBJ#=61064 D_OBJ#=61117
[W] - P_OBJ#=61065 D_OBJ#=61118
[W] - P_OBJ#=61066 D_OBJ#=61119
[W] - P_OBJ#=61067 D_OBJ#=61120
[W] - P_OBJ#=61068 D_OBJ#=61121
[W] - P_OBJ#=61069 D_OBJ#=61122
[W] - P_OBJ#=61070 D_OBJ#=61123
[W] - P_OBJ#=61071 D_OBJ#=61124
[W] - P_OBJ#=61072 D_OBJ#=61125
[W] - P_OBJ#=61073 D_OBJ#=61126
[W] - P_OBJ#=61074 D_OBJ#=61127
[W] - P_OBJ#=61075 D_OBJ#=61128
[W] - P_OBJ#=61076 D_OBJ#=61129
[W] - P_OBJ#=61077 D_OBJ#=61130
[W] - P_OBJ#=61078 D_OBJ#=61131
[W] - P_OBJ#=61079 D_OBJ#=61132
[W] - P_OBJ#=61135 D_OBJ#=61141
[W] - P_OBJ#=61136 D_OBJ#=61142
[W] - P_OBJ#=61137 D_OBJ#=61143
[W] - P_OBJ#=61138 D_OBJ#=61144
[W] - P_OBJ#=61139 D_OBJ#=61145
[W] - P_OBJ#=61140 D_OBJ#=61146
.- CheckDual ... 1101000600 <= *All Rel* : Ok
.- ObjectNames ... 1101000600 <= *All Rel* : Ok
.- BadCboHiLo ... 1101000600 <= *All Rel* : Ok
.- ChkIotTs ... 1101000600 <= *All Rel* : Ok
.- NoSegmentIndex ... 1101000600 <= *All Rel* : Ok
.- BadNextObject ... 1101000600 <= *All Rel* : Ok
.- OrphanIndopt ... 1101000600 > 902000800 : n/a
.- UpgFlgBitTmp ... 1101000600 > 1001000100 : n/a
.- RenCharView ... 1101000600 > 1001000100 : n/a
.- Upg9iTab$ ... 1101000600 > 902000400 : n/a
.- Upg9iTsInd ... 1101000600 > 902000500 : n/a
.- Upg10gInd$ ... 1101000600 > 1002000000 : n/a
.- DroppedROTS ... 1101000600 <= *All Rel* : Ok
.- ChrLenSmtcs ... 1101000600 <= 1101000600 : Ok
.- FilBlkZero ... 1101000600 <= *All Rel* : Ok
.- DbmsSchemaCopy ... 1101000600 <= *All Rel* : Ok
Found 0 potential problem(s) and 58 warning(s)
Contact Oracle Support with the output
to check if the above needs attention or not
PL/SQL procedure successfully completed.
SQL> spool off
评论暂时关闭