修改schema后，删除物化视图的表报错

如果利用user$ 系统视图修改schema后，如果存在物化视图，在删除修改后的用户时就会报错如下：  

ORA-00600: 内部错误代码, 参数:[kntgMvLogObjn], [0], [], [], [], [], [], [], [], [], [], []

 

 

一、问题：

把bbb用户改名为aaa(用的update这个user$表的user#字段方法)，此时修改后的aaa用户有物化视图

 

1、删除用户  aaa，报错

SQL> drop user aaa cascade;

drop user aaa cascade

ORA-00600: 内部错误代码, 参数:[kntgMvLogObjn], [0], [], [], [], [], [], [], [], [], [], []

2、拿出物化视图的表 删除 错误一致

SQL> drop table aaa.t1 purge

drop table aaa.t1 purge

ORA-00600: 内部错误代码, 参数:[kntgMvLogObjn], [0], [], [], [], [], [], [], [], [], [], []

 

二：解决方案：

 

1、参照官方解决方案：ID 1612402.1

Cause

Dictionary information related to amaterialized view log defined on the table is partly missing due to a rename ofa table while a materialized view log was still defined on it and afterwardstrying to drop the table.

The MLOG$ entry didn't exist but the MLOG$_... segment in OBJ$ still existed,and the table referenced in its flags that it uses a materialized view log.

 

Solution

To implement a solution for this issueplease execute the following steps in the given order:

drop the MLOG$_... segment from thedatabase using a DROP TABLE command

create a new materialized view log usingCREATE MATERIALIZED VIEW LOG ON <table> WITH ROWID;

drop the table with the materializedview log defined on it

 

 

2、建立物化视图日志

SQL> CREATE MATERIALIZED VIEW LOGON aaa.t1 WITH ROWID;

Materialized view log created

3、再次删除表成功

SQL> drop table aaa.t1 purge;

Table dropped