Trouble shooting ora-29471

Question

Certain sessions cause ORA-29471 because dbms_sql inoperable for those sessions. We are facing this error in our application for few records.

How this can be troubleshooted? How we can identify a particular session has no access on DBMS_SQL? Do we have any attribute/flag at session level?

Below link provides a way to reproduce this problem locally. Reproduce

Answer 1

The error is at runtime. It's possible you cannot guess it's going to happen before it happens. Maybe your solution is to have a single block checking with the cursor id you want to open dbms_sql.is_open(c_id).

But if this is what you are looking for, here is how to find the list of opened cursors:

select a.value, s.username, s.sid, s.serial#
  from v$sesstat a, v$statname b, v$session s
 where a.statistic# = b.statistic#  and s.sid=a.sid
   and b.name = 'opened cursors current'
;

You can also access v$open_cursor to count them:

SELECT *
  FROM v$open_cursor oc, v$session s
 WHERE oc.sid = s.sid
order by 3,2;

Hope this helps you tweaking something to check if the expected cursor is used.

Answer 2

Officially, once the ORA-29471 has been raised, your session can't use DBMS_SQL again.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i1026408

ORA-29471 DBMS_SQL access denied: This is raised if an invalid cursor ID number is detected. Once a session has encountered and reported this error, every subsequent DBMS_SQL call in the same session will raise this error, meaning that DBMS_SQL is non-operational for this session.

In practice, you can get away with a dbms_session.modify_package_state that will clear session state across all packages, closing all open cursors etc. Depending on your situation that may be more manageable than dropping/opening database connections.

declare
  c_1 number := 5;
  l_res boolean;
begin
  l_res := dbms_sql.is_open(c_1);
end;
/

declare
  c_2 number;
begin
  c_2 := dbms_sql.open_cursor();
end;
/

begin
 dbms_session.modify_package_state(dbms_session.reinitialize);
end;
/

declare
  c_2 number;
begin
  c_2 := dbms_sql.open_cursor();
end;
/