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
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.
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;
/
The error occurs when a given cursor argument was either not opened by DBMS_SQL or is already closed. One possible cause could be that the application opens the cursor and passes it on to some other code. That other code then closes the cursor and returns to your application which now has no longer access to that closed cursor.
Without any snippets of your actual code failing, it will be difficult to help you. However, to debug this look for the first instance of this error in a session and see what cursor number was passed into DBMS_SQL. That cursor number is incorrect.
Note that the state of DBMS_SQL is invalidated for that session after this error occurs. With other words, no subsequent executions of DBMS_SQL within that session are allowed either anymore and will raise the same error. This is done for security purposes: Some piece of malicious code could have tried to get hold of your cursor to expose sensitive data, for example. DBMS_SQL will issue this error and then lock down so that the malicious code is prevented from any other malicious SQL attacks. You will have to reconnect, therefore clearing the entire session state and kicking out the malicious code as well, to have DBMS_SQL access again. Other concurrently running sessions are not affected by this. They continue to operate normal in their own private and secure session state.
the oracle errorstack may help you.
like this:
alter system set events '29471 trace name errorstack level 1';
if ora-29471 really occured,
the information will be printed in alert log with related diagnose trace file.
in the diagnose trace file,
you could easily get the information which needed.
