oracle obj$,obj$基表-删除多个字典基表上的记录

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官网

阅读(2573) | 评论(0) | 转发(0) |