oracle 12.2 pdb sqlplus 连接正常, sqldeveloper plsql 连接 hang住

最新推荐文章于 2022-07-28 17:22:02 发布

weixin_30496431

最新推荐文章于 2022-07-28 17:22:02 发布

阅读量281

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/cqdba/p/10980155.html

版权

oracle 12.2 pdb sqlplus 连接正常, sqldeveloper plsql 连接 hang住

select event.count(*) from v$session where  wait_class#<>6  group by event ; 
event                                 count(*)
--------------------------------------------------
cursor:mutex S                         2    
cursor:mutex X                         31

原因： _cursor_obsolete_threshold 12.2 以前默认值为1024 ， 12.2 后的默认值为8192 参考文档(2298504.1 )

set linesize 200
col parameter for a35
col "session value" for a15
col "instance value" for a15
col KSPPDESC for a50
SELECT a.ksppinm "parameter",b.ksppstvl "session value",c.ksppstvl "instance value",a.KSPPDESC 
    FROM x$ksppi a, x$ksppcv b, x$ksppsv c 
    WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '_cursor_obsolete_threshold';

解决办法:

1. 修改 _cursor_obsolete_threshold 修改1024 或100

再重启数据库

Alter system set "_cursor_obsolete_threshold"=1024 scope=spfile

2. 临时解决方案：

alter system flush   shared_pool  ;
alter system flush   shared_pool  ;

3. 通过SQLID 找到内存区域，并清理,（参考文档 2542447.1）

 SQL> select address,hash_value,version_count from v$sqlarea where sql_id='a9x5sbz88kmfh';

ADDRESS HASH_VALUE VERSION_COUNT
---------------- ---------- -------------
000000006BFFAC00 3498659280 2

SQL> exec dbms_shared_pool.purge('<address,hash_value>','C');
Ex:
SQL> exec dbms_shared_pool.purge('000000006BFFAC00,3498659280','C');

PL/SQL procedure successfully completed.