Oracle segment_name为数字的怪象

来源：互联网发布：php api接口实例编辑：程序博客网时间：2024/06/01 08:21

现象描述：
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

0 0