Kintana Script : Handle an Oracle ORA-02020 'too many database links in use' error

Purpose

If you get an Oracle ORA-02020 too many database links in use error, you'll need to check the current value before updating the init,ora open_links startup parameter.

The NAME open_links record's VALUE field shows the current maximum number of open links allowed per session.

When you update the value, i suggest you also note the update date and the previous value in the UPDATE_COMMENTS field.

Remember that the parameter change will not take effect until the instance is shutdown and restarted.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.0.

Usage

Paste the script into Toad

select * from v$parameter
where name like '%open_links%'

select distinct s.username,
       s.osuser,
       s.machine,
       substr(s.program,1,4) prog,
       s.sid,
       s.serial#,
       decode(trunc(logon_time), trunc(sysdate),to_char(logon_time,’23:00’),
       to_char(logon_time, ‘24-OCT’) ) logon,
       floor(last_call_et/3600) || ‘:’ ||
       floor(mod(last_call_et,3600)/60) || ‘:’ ||
       mod(mod(last_call_et,3600),60) run_time,
       last_call_et,
       q.sql_text
from v$session s, v$sql q
where s.sql_address= q.address
  and s.status = ‘ACTIVE’
  and s.username is not null
  and q.sql_text not like ‘%pipe%’
order by last_call_et desc;

Download

KintanaScripts.zip (58 Kb)