|
[20160719]对象C_OBJ#_INTCOL#有坏块.txt
--论坛上有人问对象C_OBJ#_INTCOL#有坏块,链接如下:
--http://www.itpub.net/thread-2063836-1-1.html
--自己也探究看看:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------- -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> select * from dba_objects where object_name='C_OBJ#_INTCOL#';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------ -------------------- ---------- ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SYS C_OBJ#_INTCOL# 444 444 CLUSTER 2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID N N N 5
--不在SYS.BOOTSTRAP$的对象内。里面仅仅有表SYS.HISTGRM$。
CREATE TABLE SYS.HISTGRM$
(
OBJ# NUMBER,
COL# NUMBER,
ROW# NUMBER,
BUCKET NUMBER,
ENDPOINT NUMBER,
INTCOL# NUMBER,
EPVALUE VARCHAR2(1000 BYTE),
SPARE1 NUMBER,
SPARE2 NUMBER
)
CLUSTER SYS.C_OBJ#_INTCOL#(OBJ#, INTCOL#);
--这个表是直方图的内容。我觉得如果是几个块问题,你可以看看涉及obj#对象,删除这些对象的直方图,也就是分析对应表时设置
--Method_Opt => 'FOR ALL COLUMNS SIZE 1 '.
--以前按照eygle的方法我在测试环境做过,遇到一些问题,机器无法登录的情况,我把当时的测试摘录下来版本应该10.2.0.4,仅仅作为你的参考:
--你可以自己测试看看:
http://www.eygle.com/archives/20 ... 3_c_obj_intcol.html
alter system set event='38003 trace name context forever, level 10' scope=spfile;
$ oerr ora 38003
38003, 00000, "CBO Disable column stats for the dictionary objects in recursive SQL"
// *Cause:
// *Action:
然后重启数据库,可以Truncate该对象:
SYS@test> alter system set event='38003 trace name context forever, level 10' scope=spfile;
System altered.
SYS@test> startup
ORACLE instance started.
Total System Global Area 473956352 bytes
Fixed Size 2084776 bytes
Variable Size 142606424 bytes
Database Buffers 318767104 bytes
Redo Buffers 10498048 bytes
Database mounted.
Database opened.
SYS@test> show parameter event
NAME TYPE VALUE
------ ------- -------------------------------------------
event string 38003 trace name context forever, level 10
SYS@test> truncate cluster SYS.C_OBJ#_INTCOL#;
Cluster truncated.
SYS@test> alter system reset event scope=spfile sid='*';
System altered.
--使用toad连接报错。
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []
ORA-06512: at line 138
--估计是bug
http://www.askmaclean.com/archiv ... rguments-15160.html
--好像当时许多语句无法执行,必须先设置
alter system set optimizer_mode=rule scope=memory ;
--分析系统表sys,还有应用表,然后OK了。 |
|