Total System Global Area 1065353216 bytes
Fixed Size 2089336 bytes
Variable Size 486542984 bytes
Database Buffers 570425344 bytes
Redo Buffers 6295552 bytes
Database mounted.
Database opened.
SQL> select count(*) from user$ u, obj$ o
2 where u.user# (+)=o.owner# and o.type#=10 and not exists
3 (select p_obj# from dependency$ where p_obj# = o.obj#);
COUNT(*)
----------
5000
SQL> /
COUNT(*)
----------
5000
SQL> /
COUNT(*)
----------
4951
SQL> oradebug setospid 18457;
Oracle pid: 8, Unix process pid: 18457, image: oracle@rh2.oracle.com (SMON)
SQL> oradebug event 10046 trace name context forever ,level 1;
Statement processed.
SQL> oradebug tracefile_name;
/s01/admin/G10R2/bdump/g10r2_smon_18457.trc
select o.owner#,
o.obj#,
decode(o.linkname,
null,
decode(u.name, null, 'SYS', u.name),
o.remoteowner),
o.name,
o.linkname,
o.namespace,
o.subname
from user$ u, obj$ o
where u.use r#(+) = o.owner#
and o.type# = :1
and not exists
(select p_obj# from dependency$ where p_obj# = o.obj#)
order by o.obj#
for update
select null
from obj$
where obj# = :1
and type# = :2
and obj# not in
(select p_obj# from dependency$ where p_obj# = obj$.obj#)
delete from obj$ where obj# = :1
/* 删除过程其实较为复杂,可能要删除多个字典基表上的记录 */
现象
我们可以通过以下查询来了解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';本文选自cuug官网
阅读(3168) | 评论(0) | 转发(0) |