obj$基表中NON-EXISTENT对象现象

obj$基表中NON-EXISTENT对象现象详解。

 

我们可以通过以下查询来了解obj$基表中NON-EXISTENT对象的条目总数(type#=10)，若这个总数在不断减少说明smon正在执行清理工作

 

    select trunc(mtime), substr(name, 1, 3) name, count(*)

      from obj$

     where type# = 10

       and not exists (select * from dependency$ where obj# = p_obj#)

     group by trunc(mtime), substr(name, 1, 3);

 

      select count(*)

        from user$ u, obj$ o

       where u.user#(+) = o.owner#

         and o.type# = 10

         and not exists

       (select p_obj# from dependency$ where p_obj# = o.obj#);

 

如何禁止SMON清理obj$基表

 

我们可以通过设置诊断事件event=’10052 trace name context forever’来禁止SMON清理obj$基表，当我们需要避免SMON因cleanup obj$的相关代码而意外终止或spin从而开展进一步的诊断时可以设置该诊断事件。在Oracle并行服务器或RAC环境中，也可以设置该事件来保证只有特定的某个节点来执行清理工作。

10052, 00000, "don't clean up obj$"

 

alter system set events '10052 trace name context forever, level 65535';

 

Problem Description: We are receiving the below warning during db startup:

WARNING: kqlclo() has detected the following :

Non-existent object 37336 NOT deleted because an object

of the same name exists already.

Object name: PUBLIC.USER$

 

This is caused by the SMON trying to cleanup the SYS.OJB$.

SMON cleans all dropped objects which have a SYS.OBJ$.TYPE#=10.

This can happen very often when you create an object that have the same name as a public synonym.

 

When SMON is trying to remove non-existent objects and fails because there are duplicates,

multiple nonexistent objects with same name.

This query will returned many objects with same name under SYS schema:

 

select o.name,u.user# from user$ u, obj$ o where u.user# (+)=o.owner# and o.type#=10

and not exists (select p_obj# from dependency$ where p_obj# = o.obj#);

 

To cleanup this message:

 

Take a full backup of the database - this is crucial. If anything goes wrong during this procedure,

your only option would be to restore from backup, so make sure you have a good backup before proceeding.

We suggest a COLD backup. If you plan to use a HOT backup, you will have to restore point in time if any problem happens

 

Normally DML against dictionary objects is unsupported,

but in this case we know exactly what the type of corruption,

also you are instructing to do this under guidance from Support.

 

Data dictionary patching must be done by an experienced DBA.

This solution is unsupported.

It means that if there were problems after applying this solution, a database backup must be restored.

Oracle 10g 补丁:http://www.cuug.com/