select ksppinm name, ksppstvl value, ksppdesc description

from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and ( ksppinm like '&1' or ksppdesc like '&1' ) order by name;

首先由隐含参数获知该数据库只有一个share pool NAME                                          VALUE                DESCRIPTION --------------------------------------------- -------------------- ---------------------------------------------------------------------- _kghdsidx_count                               1                    max kghdsidx count

开启两个数据库会话

Session 1 获取share pool latch的内存地址 SQL> select addr,child# from v$latch_children where name ='shared pool' and child#=1;

ADDR                 CHILD#

---------------- ---------- 0000000060103670          1

在session 2中执行如下

SQL> oradebug setmypid Statement processed. SQL> oradebug poke 0x0000000060103670 4 1 BEFORE: [060103670, 060103674) = 00000000 AFTER:  [060103670, 060103674) = 00000001 SQL> oradebug dump processstate 10 Statement processed. SQL> oradebug tracefile_name /oracle10g/diag/rdbms/test11g/test11g/trace/test11g_ora_1061.trc SQL> oradebug poke 0x0000000060103670 4 0; BEFORE: [060103670, 060103674) = 00000001 AFTER:  [060103670, 060103674) = 00000000

然后在session 1中执行，其中select语句一直都在hang，直到session 2中执行复位操作

SQL> set timing on SQL> select count(*) from v$version;   

  COUNT(*)

----------          5

Elapsed: 00:01:03.04

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