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

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.