模拟ORA-00942错误引起的ORA-600(16500)

早上群里的一哥们说要删除一个表的数据的时候报错ORA-00942: table or view does not exist，但是select查询的时候，却能查出数据，
SQL> select count(*) from emp;
  COUNT(*)
----------
        13

SQL> delete emp;
delete emp
       *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;

System altered.

SQL> delete emp;
delete emp
       *
ERROR at line 1:
ORA-00942: table or view does not exist

最后检查发现是跟物化试图有关，由于用了非正常手段删除了物化试图的log，才导致这个错误

解决的方法不难：
SQL> drop materialized view log on emp;
drop MATERIALIZED VIEW LOG ON emp
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

这里drop的时候报错
查看alter日志内容：
[oracle@goolen bdump]$ tail -30f alert_ora10g.log 
Tue Jan 13 16:26:52 2015
Errors in file /opt/app/oracle/admin/ora10g/udump/ora10g_ora_7973.trc:
ORA-00600: internal error code, arguments: [16500], [kqdobu], [D], [0], [67], [MLOG$_EMP], [], []
Tue Jan 13 16:26:55 2015
Errors in file /opt/app/oracle/admin/ora10g/udump/ora10g_ora_7973.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [16500], [kqdobu], [D], [0], [67], [MLOG$_EMP], [], []

查看trace文件：
[oracle@goolen ~]$ more /opt/app/oracle/admin/ora10g/udump/ora10g_ora_7973.trc
/opt/app/oracle/admin/ora10g/udump/ora10g_ora_7973.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/app/oracle/product/10.2.0
System name:    Linux
Node name:      goolen
Release:        2.6.32-279.el6.x86_64
Version:        #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine:        x86_64
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 7973, image: oracle@goolen (TNS V1-V3)

*** ACTION NAME:() 2015-01-13 16:26:52.616
*** MODULE NAME:(SQL*Plus) 2015-01-13 16:26:52.616
*** SERVICE NAME:(SYS$USERS) 2015-01-13 16:26:52.616
*** SESSION ID:(142.1925) 2015-01-13 16:26:52.616
*** 2015-01-13 16:26:52.616
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [16500], [kqdobu], [D], [0], [67], [MLOG$_EMP], [], []
Current SQL statement for this session:
drop MATERIALIZED VIEW LOG ON emp
----- Call Stack Trace -----

查询了下mos，发现这个600错误应该跟回收站有关
试着清空一下回收站
SQL> PURGE dba_recyclebin;

DBA Recyclebin purged.

SQL> drop MATERIALIZED VIEW LOG ON emp;

Materialized view log dropped.

SQL> delete emp;

13 rows deleted.

SQL> roll;
Rollback complete.

删除了物化视图的log后，delete操作也能正常完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1400570/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1400570/