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.
