Know which Procedure or session is leaving open cursors

Question

I wonder if there is any way to find out which session or Procedure(s) are leaving cursors open on Oracle, sometimes (not always) error occurs:

ORA-01000: Ximum open Cursors exceeded

Answer 1

As Anthony mentioned, you can make a query to identify (logged in as system):

-- recuperar sessao para matar lock
select
   sid
   ||','||
   serial# sessao
from
   v$session
where
   sid in ( 
      SELECT a.SESSION_ID      
      FROM V$LOCKED_OBJECT A, ALL_OBJECTS B      
      WHERE A.OBJECT_ID = B.OBJECT_ID   
    );

If you want to "kill" what’s blocked:

-- matar lock   
alter system kill session '99,16886';  --sessao