ORA-00600: internal error code, arguments: [13013], [5001], [8976], [12590167], [8], [12588885], [17], []
初步判断应该是某个表产生坏块或者index失效导致的，以为是每个整点都会出现这样的错误，应该是oracle后台在收集统计信息的时候报错，历史统计表有问题
1、查询
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_ID=8976;
OWNER OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
SYS WRH$_SQL_PLAN TABLE
2、分析表
SQL> analyze table sys.WRH$_SQL_PLAN validate structure cascade online;
analyze table sys. validate structure cascade online
*
ORA-01499:
ORA-01499表示表上的索引失效；ORA-01498表示表上存在坏块
索引失效需要重建index；表失效需要将坏块找出来recovery，或者将原表rename后重建table，或者使用dbms_repair 修复
3、重建索引
SQL> select table_name ,index_name,STATUS from dba_indexes where table_name='WRH$_SQL_PLAN' and owner'SYS';
TABLE_NAME INDEX_NAME STATUS
--------------------------------------------------------------------------------
WRH$_SQL_PLAN SYS_IL0000008976C00038$$ VALID
WRH$_SQL_PLAN WRH$_SQL_PLAN_PK INVALID
WRH$_SQL_PLAN_PK是主键，不能直接删除，只能重建主键了
SQL> select dbms_metadata.get_ddl('CONSTRAINT','WRH$_SQL_PLAN_PK','SYS') FROM DUAL;
DBMS_METADATA.GET_DDL('CONSTRAINT','WRH$_SQL_PLAN_PK','SYS')
--------------------------------------------------------------------------------
ALTER TABLE "SYS"."WRH$_SQL_PLAN" ADD CONSTRAINT "WRH$_SQL_PLAN_PK" PRIMARY KEY ("DBID", "SQL_ID", "PLAN_HASH_VALUE",
"ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSAUX" ENABLE
ALTER TABLE WRH$_SQL_PLAN DROP PRIMARY KEY;
ALTER TABLE "SYS"."WRH$_SQL_PLAN" ADD CONSTRAINT "WRH$_SQL_PLAN_PK" PRIMARY KEY ("DBID", "SQL_ID", "PLAN_HASH_VALUE", "ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSAUX" ENABLE;
ORA-02437:
SQL> select count(1) from "SYS"."WRH$_SQL_PLAN";
COUNT(1)
----------
15420
SQL> select * from "SYS"."WRH$_SQL_PLAN" where DBID is null or SQL_ID is null or PLAN_HASH_VALUE is null
2 or id is null;
?′???¨DD
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select DBID,SQL_ID,PLAN_HASH_VALUE,ID,count(1) from "SYS"."WRH$_SQL_PLAN"
group by DBID,SQL_ID,PLAN_HASH_VALUE,ID having count(1)>1;
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 c4psbujzwz0s6 2065888885 1 2
1404529434 c4psbujzwz0s6 2065888885 5 2
1404529434 c4psbujzwz0s6 2065888885 7 2
1404529434 1m8aw84c7h15w 2970138452 0 2
1404529434 dpvv2ua0tfjcv 467914355 2 2
1404529434 dh5wy2kn3hv2c 1939782058 11 2
1404529434 8362886fysavv 3028786551 1 2
1404529434 6c06mfv01xt2h 2399945022 13 2
1404529434 6c06mfv01xt2h 2399945022 15 3
1404529434 2q93zsrvbdw48 2874733959 0 2
1404529434 b07vcvuxryvg9 2239305549 9 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 dh5wy2kn3hv2c 1939782058 1 2
1404529434 3s1yukp05bzg6 4159334603 1 2
1404529434 g250r9gxh5qpp 48104607 0 2
1404529434 g250r9gxh5qpp 48104607 3 2
1404529434 69zzbaxr91ydv 3109013023 4 2
1404529434 69zzbaxr91ydv 3109013023 18 2
1404529434 69zzbaxr91ydv 3109013023 21 2
1404529434 69zzbaxr91ydv 3109013023 38 2
1404529434 69zzbaxr91ydv 3109013023 43 2
1404529434 69zzbaxr91ydv 3109013023 45 2
1404529434 1gu8t96d0bdmu 3526770254 0 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 ga9j9xk5cy9s0 1516415349 1 2
1404529434 06a7wq8prjng7 293268181 0 2
1404529434 32mk33ry1g665 3273909651 8 2
1404529434 b07vcvuxryvg9 3293735340 3 2
1404529434 32hbap2vtmf53 3643809231 0 2
1404529434 3505vtqmvvf40 3747738893 1 2
1404529434 6c06mfv01xt2h 2399945022 10 2
1404529434 b07vcvuxryvg9 2239305549 12 2
1404529434 17smnq9r7rac6 3485277045 2 2
1404529434 dh5wy2kn3hv2c 1939782058 3 2
1404529434 gmnd4yt3c4zyw 3569023093 7 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 69zzbaxr91ydv 3109013023 7 2
1404529434 69zzbaxr91ydv 3109013023 11 2
1404529434 69zzbaxr91ydv 3109013023 17 2
1404529434 69zzbaxr91ydv 3109013023 25 2
1404529434 69zzbaxr91ydv 3109013023 27 2
1404529434 69zzbaxr91ydv 3109013023 36 2
1404529434 69zzbaxr91ydv 3109013023 46 2
1404529434 f8pavn1bvsj7t 1224215794 0 2
1404529434 ga9j9xk5cy9s0 1516415349 2 2
1404529434 1gfaj4z5hn1kf 1110520934 0 2
1404529434 1gfaj4z5hn1kf 1110520934 1 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 32mk33ry1g665 3273909651 0 2
1404529434 frmuarw3st23v 2909482823 0 2
1404529434 32mk33ry1g665 3273909651 3 2
1404529434 32mk33ry1g665 3273909651 9 2
1404529434 32hbap2vtmf53 3643809231 2 2
1404529434 b07vcvuxryvg9 3293735340 10 2
1404529434 c4psbujzwz0s6 2065888885 4 2
1404529434 bwsx6utfbh15q 1020111780 1 2
1404529434 409wc1d7nz1c4 4273315616 0 2
1404529434 cfz686a6qp0kg 3679656590 7 2
1404529434 dh5wy2kn3hv2c 1939782058 5 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 dh5wy2kn3hv2c 1939782058 8 2
1404529434 3dcbnjcwygb1f 669385525 0 2
1404529434 cvn54b7yz0s8u 2334475966 3 3
1404529434 6c06mfv01xt2h 2399945022 2 2
1404529434 6c06mfv01xt2h 2399945022 3 2
1404529434 6c06mfv01xt2h 2399945022 9 2
1404529434 2q93zsrvbdw48 2874733959 2 2
1404529434 2q93zsrvbdw48 2874733959 3 2
1404529434 cp3gpd7z878w8 2994066523 0 2
1404529434 cp3gpd7z878w8 2994066523 3 2
1404529434 b07vcvuxryvg9 2239305549 5 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 g250r9gxh5qpp 48104607 4 2
1404529434 g250r9gxh5qpp 48104607 6 2
1404529434 g250r9gxh5qpp 48104607 7 2
1404529434 9tgxfvwp01806 4200007576 0 2
1404529434 69zzbaxr91ydv 3109013023 1 2
1404529434 69zzbaxr91ydv 3109013023 6 2
1404529434 69zzbaxr91ydv 3109013023 40 2
1404529434 69zzbaxr91ydv 3109013023 41 2
1404529434 bkdusjx00dsmc 3751820903 1 2
1404529434 bkdusjx00dsmc 3751820903 5 2
1404529434 06a7wq8prjng7 293268181 2 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 06a7wq8prjng7 293268181 3 2
1404529434 3kjbw2svxydst 1991257954 1 2
1404529434 3kjbw2svxydst 1991257954 2 2
1404529434 gb4qjzp9su4h4 802427389 5 2
1404529434 gb4qjzp9su4h4 802427389 8 2
1404529434 32hbap2vtmf53 3643809231 3 2
1404529434 b1wc53ddd6h3p 1637390370 1 2
1404529434 6cr55dpp3n44a 2853959010 0 2
1404529434 6cr55dpp3n44a 2853959010 1 2
1404529434 c4psbujzwz0s6 2065888885 6 2
1404529434 9p6bq1v54k13j 415205717 0 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 9p6bq1v54k13j 415205717 1 2
1404529434 bwsx6utfbh15q 1020111780 0 2
1404529434 c4psbujzwz0s6 2065888885 0 2
1404529434 cvn54b7yz0s8u 2334475966 1 3
1404529434 cvn54b7yz0s8u 2334475966 2 3
1404529434 6c06mfv01xt2h 2399945022 5 2
1404529434 2q93zsrvbdw48 2874733959 1 2
1404529434 b07vcvuxryvg9 2239305549 1 2
1404529434 dh5wy2kn3hv2c 1939782058 2 2
1404529434 gmnd4yt3c4zyw 3569023093 6 2
1404529434 69zzbaxr91ydv 3109013023 15 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 69zzbaxr91ydv 3109013023 16 2
1404529434 69zzbaxr91ydv 3109013023 35 2
1404529434 69zzbaxr91ydv 3109013023 47 2
1404529434 bkdusjx00dsmc 3751820903 7 2
1404529434 04xtrk7uyhknh 2853959010 2 2
1404529434 ga9j9xk5cy9s0 1516415349 3 2
1404529434 3kjbw2svxydst 1991257954 3 2
1404529434 32mk33ry1g665 3273909651 1 2
1404529434 32mk33ry1g665 3273909651 5 2
1404529434 32mk33ry1g665 3273909651 6 2
1404529434 36g2yq0mxjx2y 1568359520 0 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 36g2yq0mxjx2y 1568359520 1 2
1404529434 0xwby571mq3n2 2709293936 0 2
1404529434 b07vcvuxryvg9 3293735340 1 2
1404529434 32hbap2vtmf53 3643809231 1 2
1404529434 b07vcvuxryvg9 3293735340 9 2
1404529434 b07vcvuxryvg9 3293735340 11 2
1404529434 1m8aw84c7h15w 2970138452 2 2
1404529434 cfz686a6qp0kg 3679656590 6 2
1404529434 3dcbnjcwygb1f 669385525 3 2
1404529434 39m4sx9k63ba2 323350262 0 2
1404529434 6c06mfv01xt2h 2399945022 0 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 8z5h7ks4vc4c2 530984358 1 2
1404529434 8z5h7ks4vc4c2 530984358 3 2
1404529434 cp3gpd7z878w8 2994066523 1 2
1404529434 3s1yukp05bzg6 4159334603 0 2
1404529434 525j9xq684qa0 3980178854 47 2
1404529434 g250r9gxh5qpp 48104607 2 2
1404529434 g250r9gxh5qpp 48104607 5 2
1404529434 69zzbaxr91ydv 3109013023 13 2
1404529434 69zzbaxr91ydv 3109013023 19 2
1404529434 69zzbaxr91ydv 3109013023 24 2
1404529434 69zzbaxr91ydv 3109013023 28 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 69zzbaxr91ydv 3109013023 30 2
1404529434 69zzbaxr91ydv 3109013023 39 2
1404529434 bkdusjx00dsmc 3751820903 4 2
1404529434 f8pavn1bvsj7t 1224215794 2 2
1404529434 b07vcvuxryvg9 3293735340 0 2
1404529434 gb4qjzp9su4h4 802427389 4 2
1404529434 b07vcvuxryvg9 3293735340 2 2
1404529434 b07vcvuxryvg9 3293735340 5 2
1404529434 b07vcvuxryvg9 3293735340 8 2
1404529434 3505vtqmvvf40 3747738893 0 2
1404529434 c4psbujzwz0s6 2065888885 3 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 dh5wy2kn3hv2c 1939782058 9 2
1404529434 8362886fysavv 3028786551 2 2
1404529434 6c06mfv01xt2h 2399945022 1 2
1404529434 6c06mfv01xt2h 2399945022 4 2
1404529434 6c06mfv01xt2h 2399945022 6 2
1404529434 6c06mfv01xt2h 2399945022 7 2
1404529434 6c06mfv01xt2h 2399945022 8 2
1404529434 6c06mfv01xt2h 2399945022 11 2
1404529434 asvzxj61dc5vs 3028786551 0 2
1404529434 b07vcvuxryvg9 2239305549 4 2
1404529434 b07vcvuxryvg9 2239305549 6 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 b07vcvuxryvg9 2239305549 7 2
1404529434 17smnq9r7rac6 3485277045 0 2
1404529434 69zzbaxr91ydv 3109013023 12 2
1404529434 69zzbaxr91ydv 3109013023 26 2
1404529434 69zzbaxr91ydv 3109013023 29 2
1404529434 69zzbaxr91ydv 3109013023 34 2
1404529434 69zzbaxr91ydv 3109013023 44 2
1404529434 bkdusjx00dsmc 3751820903 2 2
1404529434 bkdusjx00dsmc 3751820903 6 2
1404529434 bkdusjx00dsmc 3751820903 8 2
1404529434 f8pavn1bvsj7t 1224215794 1 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 32mk33ry1g665 3273909651 4 2
1404529434 32mk33ry1g665 3273909651 7 2
1404529434 0xwby571mq3n2 2709293936 1 2
1404529434 0xwby571mq3n2 2709293936 2 2
1404529434 gb4qjzp9su4h4 802427389 2 2
1404529434 gb4qjzp9su4h4 802427389 7 2
1404529434 gb4qjzp9su4h4 802427389 9 2
1404529434 b07vcvuxryvg9 3293735340 7 2
1404529434 b1wc53ddd6h3p 1637390370 2 2
1404529434 6cr55dpp3n44a 2853959010 2 2
1404529434 dpvv2ua0tfjcv 467914355 0 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 c7sn076yz7030 3210068263 0 2
1404529434 3dcbnjcwygb1f 669385525 1 2
1404529434 8z5h7ks4vc4c2 530984358 0 2
1404529434 8z5h7ks4vc4c2 530984358 2 2
1404529434 cp3gpd7z878w8 2994066523 2 2
1404529434 cp3gpd7z878w8 2994066523 4 2
1404529434 b07vcvuxryvg9 2239305549 3 2
1404529434 b07vcvuxryvg9 2239305549 10 2
1404529434 b07vcvuxryvg9 2239305549 11 2
1404529434 17smnq9r7rac6 3485277045 1 2
1404529434 17smnq9r7rac6 3485277045 3 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 dh5wy2kn3hv2c 1939782058 0 2
1404529434 5ms1dhxbadq64 1341812362 0 2
1404529434 3s1yukp05bzg6 4159334603 2 2
1404529434 525j9xq684qa0 3980178854 46 2
1404529434 g250r9gxh5qpp 48104607 1 2
1404529434 69zzbaxr91ydv 3109013023 8 2
1404529434 69zzbaxr91ydv 3109013023 10 2
1404529434 69zzbaxr91ydv 3109013023 14 2
1404529434 69zzbaxr91ydv 3109013023 20 2
1404529434 69zzbaxr91ydv 3109013023 22 2
1404529434 69zzbaxr91ydv 3109013023 31 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 69zzbaxr91ydv 3109013023 32 2
1404529434 69zzbaxr91ydv 3109013023 33 2
1404529434 69zzbaxr91ydv 3109013023 37 2
1404529434 04xtrk7uyhknh 2853959010 1 2
1404529434 1c1v6tv5am9sg 1341812362 0 3
1404529434 c6awqs517jpj0 1780865333 1 2
1404529434 06a7wq8prjng7 293268181 1 2
1404529434 1gfaj4z5hn1kf 1110520934 2 2
1404529434 gb4qjzp9su4h4 802427389 1 2
1404529434 b07vcvuxryvg9 3293735340 6 2
1404529434 b1wc53ddd6h3p 1637390370 0 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 b07vcvuxryvg9 3293735340 12 2
1404529434 1c1v6tv5am9sg 1341812362 1 2
1404529434 dpvv2ua0tfjcv 467914355 1 2
1404529434 c4psbujzwz0s6 2065888885 2 2
1404529434 c4psbujzwz0s6 2065888885 8 2
1404529434 1m8aw84c7h15w 2970138452 1 2
1404529434 dh5wy2kn3hv2c 1939782058 4 2
1404529434 dh5wy2kn3hv2c 1939782058 6 2
1404529434 dh5wy2kn3hv2c 1939782058 7 2
1404529434 dh5wy2kn3hv2c 1939782058 10 2
1404529434 8362886fysavv 3028786551 0 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 3dcbnjcwygb1f 669385525 2 2
1404529434 3dcbnjcwygb1f 669385525 4 2
1404529434 cvn54b7yz0s8u 2334475966 0 3
1404529434 6c06mfv01xt2h 2399945022 12 2
1404529434 6c06mfv01xt2h 2399945022 14 3
1404529434 asvzxj61dc5vs 3028786551 1 2
1404529434 asvzxj61dc5vs 3028786551 2 2
1404529434 b07vcvuxryvg9 2239305549 2 2
1404529434 b07vcvuxryvg9 2239305549 8 2
1404529434 69zzbaxr91ydv 3109013023 0 2
1404529434 69zzbaxr91ydv 3109013023 2 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 69zzbaxr91ydv 3109013023 3 2
1404529434 69zzbaxr91ydv 3109013023 5 2
1404529434 69zzbaxr91ydv 3109013023 9 2
1404529434 69zzbaxr91ydv 3109013023 23 2
1404529434 69zzbaxr91ydv 3109013023 42 2
1404529434 bkdusjx00dsmc 3751820903 3 2
1404529434 04xtrk7uyhknh 2853959010 0 2
1404529434 c6awqs517jpj0 1780865333 0 2
1404529434 dcstr36r0vz0d 17720163 0 2
1404529434 3kjbw2svxydst 1991257954 0 2
1404529434 3kjbw2svxydst 1991257954 4 2
DBID SQL_ID PLAN_HASH_VALUE ID COUNT(1)
---------- -------------------------- --------------- ---------- ----------
1404529434 frmuarw3st23v 2909482823 1 2
1404529434 32mk33ry1g665 3273909651 2 2
1404529434 gb4qjzp9su4h4 802427389 0 2
1404529434 gb4qjzp9su4h4 802427389 3 2
1404529434 gb4qjzp9su4h4 802427389 6 2
1404529434 gb4qjzp9su4h4 802427389 10 2
1404529434 b07vcvuxryvg9 3293735340 4 2
1404529434 1c1v6tv5am9sg 1341812362 2 2
250 rows selected.
删除错误记录
SQL> delete "SYS"."WRH$_SQL_PLAN" a
2 where a.rowid<
3 (select max(b.rowid) from "SYS"."WRH$_SQL_PLAN" b
where a.DBID=b.DBID and a.SQL_ID=b.SQL_ID and a.PLAN_HASH_VALUE=b.PLAN_HASH_VALUE and a.ID=b.ID); 4
257 rows deleted.
SQL> commit;
Commit complete.
4、重建主键
ALTER TABLE "SYS"."WRH$_SQL_PLAN" ADD CONSTRAINT "WRH$_SQL_PLAN_PK" PRIMARY KEY ("DBID", "SQL_ID", "PLAN_HASH_VALUE", "ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSAUX" ENABLE;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25801738/viewspace-703811/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/25801738/viewspace-703811/
288
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
