ORA-1000 reported in relation to cursors associated with dbms_prvtaqis.add_proxy in an 11.1 Streams environment (Doc ID 730372.1)

Last updated on MAY 05, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.

Symptoms

In an 11.1 Streams environment over time the number of open cursors can increase and may lead to an ORA-1000 being reported even though there is no external load on the system.

The cursors will have dbms_prvtaqis.add_proxy in the sql_text

select sid from v$open_cursor where sql_text like'%dbms_prvtaqis.add_proxy%'; 

and this sid will be associated with the coordinator job_queue_process. Setting an errorstack on the ORA-1000 error via

connect / as sysdba

alter system set events '1000 trace name errorstack level 3';

will generate the following information

ORA-1000: maximum open cursors exceeded
----- Current SQL Statement for this session (sql_id=axjfm3f3unrv9) -----
begin dbms_prvtaqis.add_proxy(:1, :2, :3, :4, :5, :6, :7, :8, :9);end;
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object                         line          object
handle                        number   name
000007FF6E7B9B18   49             package body SYS.DBMS_IJOB
000007FF6E7ED718   818           package body SYS.DBMS_AQADM_SYS
000007FF6E7D8C10  708           package body SYS.DBMS_PRVTAQIS
000007FF6E7D8C10  834           package body SYS.DBMS_PRVTAQIS
000007FF6E204260    1               anonymous block


 

Call Stack:

<- kxsOpenACursor
<- kxsFastOpenCursor
<- kgscGetCursor
<- pevm_INSTC2
<- pfrinstr_INSTC2
<- pfrrun_no_tool
<- pfrrun
<-plsql_run
<- peicnt
<- kkxexe
<- opiexe
<- opiall0
<- opikpr
<- opiodr
<- rpidrus
<- rpidru
<- rpiswu2
<- kprball
<- kwqmagtadd
<- kwqmad2api
<- kwqmdac2dai
<- kwqidqbnd
<- kwqidrdq

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms