现象描述:
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 1002000100 : n/a
.- IndIndparMismatch ... 1101000600