oracle中segment_name,Oracle segment_name为数字的怪象

最新推荐文章于 2023-12-31 21:02:25 发布

李韩资

最新推荐文章于 2023-12-31 21:02:25 发布

阅读量964

点赞数

文章标签： 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

李韩资

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
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-----------------------------...
复制链接

扫一扫