Thread: [cx-oracle-users] ORA-01000: maximum open cursors exceeded error
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users
|
From: Vikrant A. <vik...@gm...> - 2011-02-08 16:22:16
|
Hi,
I am currently using Python 2.7.1 on AIX 6.1 with cx_Oracle 5.0.4 and notice
that cursor.close() is NOT decrementing the open cursor count in Oracle.
The open cursor count is only decremented when the connection itself if
closed.
The connection object is created internally by the django framework and
hence I do not have control over closing it.
There are other applications running on the same AIX box against the same
Oracle version and they seem to be working fine - no open cursor leaks.
Can somebody point me in the right direction?
The versions of the software used is shown below :
AIX VERSION
$ oslevel -r
6100-01
$ uname -a
AIX camanht1 1 6 00C70D354C00
ORACLE VERSION
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 8 06:43:25 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
PYTHON AND CX_ORACLE VERSION
$ ./python
Python 2.7.1 (r271:86832, Feb 7 2011, 06:50:53)
[GCC 4.2.4] on aix6
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> cx_Oracle.version
'5.0.4'
COMMANDS TO REPRODUCE
>>> connection=cx_Oracle.connect("VANGIA/logfire%5678@CADEVMNH")
>>> cur=connection.cursor()
>>> cur.execute("select sysdate from dual") #### Number of open cursors
is incremented
<__builtin__.OracleCursor on <cx_Oracle.Connection to VANGIA@CADEVMNH>>
>>> res=cur.fetchone()
>>> res[0]
datetime.datetime(2011, 2, 8, 6, 55, 38)
>>> cur.close() #### Number of open cursors is NOT decremented
>>> del cur #### Number of open cursors is NOT decremented
>>> del connection #### Opened cursors are released at this point
Thanks,
Vikrant Angia
|
|
From: Anthony T. <ant...@gm...> - 2011-02-08 16:34:16
|
Hi,
I don't have access to an AIX box to see if this problem is specific
to that platform....but I do use Django on Windows and Linux and do
not run into any problems. Can you show me why you believe that the
open cursor count is unchanged when calling cursor.close()? If you put
such checking code in your script I can run it on the platforms I have
access to and see if we get different results. Thanks.
Anthony
On Tue, Feb 8, 2011 at 9:22 AM, Vikrant Angia <vik...@gm...> wrote:
> Hi,
> I am currently using Python 2.7.1 on AIX 6.1 with cx_Oracle 5.0.4 and notice
> that cursor.close() is NOT decrementing the open cursor count in Oracle.
> The open cursor count is only decremented when the connection itself if
> closed.
> The connection object is created internally by the django framework and
> hence I do not have control over closing it.
> There are other applications running on the same AIX box against the same
> Oracle version and they seem to be working fine - no open cursor leaks.
> Can somebody point me in the right direction?
> The versions of the software used is shown below :
> AIX VERSION
> $ oslevel -r
> 6100-01
> $ uname -a
> AIX camanht1 1 6 00C70D354C00
>
> ORACLE VERSION
> SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 8 06:43:25 2011
> Copyright (c) 1982, 2007, Oracle. All rights reserved.
>
> Connected to:
> Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing
> options
> PYTHON AND CX_ORACLE VERSION
> $ ./python
> Python 2.7.1 (r271:86832, Feb 7 2011, 06:50:53)
> [GCC 4.2.4] on aix6
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import cx_Oracle
>>>> cx_Oracle.version
> '5.0.4'
> COMMANDS TO REPRODUCE
>>>> connection=cx_Oracle.connect("VANGIA/logfire%5678@CADEVMNH")
>>>> cur=connection.cursor()
>>>> cur.execute("select sysdate from dual") #### Number of open cursors
>>>> is incremented
> <__builtin__.OracleCursor on <cx_Oracle.Connection to VANGIA@CADEVMNH>>
>>>> res=cur.fetchone()
>>>> res[0]
> datetime.datetime(2011, 2, 8, 6, 55, 38)
>>>> cur.close() #### Number of open cursors is NOT decremented
>>>> del cur #### Number of open cursors is NOT decremented
>>>> del connection #### Opened cursors are released at this point
>
>
> Thanks,
> Vikrant Angia
> ------------------------------------------------------------------------------
> The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
> Pinpoint memory and threading errors before they happen.
> Find and fix more than 250 security defects in the development cycle.
> Locate bottlenecks in serial and parallel code that limit performance.
> http://p.sf.net/sfu/intel-dev2devfeb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
|
|
From: Vikrant A. <vik...@gm...> - 2011-02-09 00:35:43
|
I dont have code in Python to check it but I use the following SQL query to
check the open cursors at each step.
The value of total_cur shows the number of open cursors when you run it.
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
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'
group by s.username, s.machine
order by 1 desc;
I think the problem is specific to AIX because I do the same thing on Ubuntu
and it works just fine (decrements the open cursors at cursor.close())
On Ubuntu the software versions I have are the following :
> uname -a
Linux lgfdevbox 2.6.32-24-generic #42-Ubuntu SMP Fri Aug 20 14:24:04 UTC
2010 i686 GNU/Linux
> python
Python 2.6.5 (r265:79063, Apr 16 2010, 13:09:56)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> cx_Oracle.version
'5.0.3'
>>>
I am running Oracle 10g XE database on my box
> sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 8 19:28:43 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
I dont know if the build output of cx_Oracle would help, but here it is ....
(It throws a couple of duplicate symbol warnings, but cx_Oracle.so is built
successfully)
$ python setup.py build
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.aix-6.1-2.7-11g
gcc -pthread -fno-strict-aliasing -g -O2 -DNDEBUG -g -fwrapv -O3 -Wall
-Wstrict-prototypes -I/software/oracle/product/11.1.0/client_1/rdbms/demo
-I/software/oracle/product/11.1.0/client_1/rdbms/public
-I/app/wms/wm/lgf/install_again/Python-2.7.1/Include
-I/app/wms/wm/lgf/install_again/Python-2.7.1 -c cx_Oracle.c -o
build/temp.aix-6.1-2.7-11g/cx_Oracle.o -DBUILD_VERSION=5.0.4 -qcpluscmt
gcc: unrecognized option '-qcpluscmt'
In file included from
/software/oracle/product/11.1.0/client_1/rdbms/public/oci.h:2962,
from cx_Oracle.c:10:
/software/oracle/product/11.1.0/client_1/rdbms/public/ociap.h:10700:
warning: function declaration isn't a prototype
/software/oracle/product/11.1.0/client_1/rdbms/public/ociap.h:10706:
warning: function declaration isn't a prototype
creating build/lib.aix-6.1-2.7-11g
./Modules/ld_so_aix gcc -pthread -bI:Modules/python.exp -L. -L . -L
/app/wms/wm/lgf/install_again/usr/lib build/temp.aix-6.1-2.7-11g/cx_Oracle.o
-L/software/oracle/product/11.1.0/client_1/lib32
-L/software/oracle/product/11.1.0/client_1 -lclntsh -o
build/lib.aix-6.1-2.7-11g/cx_Oracle.so
ld: 0711-224 WARNING: Duplicate symbol: .bcopy
ld: 0711-224 WARNING: Duplicate symbol: .fres
ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more
information.
At this time, I am trying to compile the Oracle Instant Client on AIX (just
to get everything compiled using the same gcc compiler).
Thanks much for your help,
Vikrant Angia
On Tue, Feb 8, 2011 at 11:34 AM, Anthony Tuininga <
ant...@gm...> wrote:
> Hi,
>
> I don't have access to an AIX box to see if this problem is specific
> to that platform....but I do use Django on Windows and Linux and do
> not run into any problems. Can you show me why you believe that the
> open cursor count is unchanged when calling cursor.close()? If you put
> such checking code in your script I can run it on the platforms I have
> access to and see if we get different results. Thanks.
>
> Anthony
>
> On Tue, Feb 8, 2011 at 9:22 AM, Vikrant Angia <vik...@gm...>
> wrote:
> > Hi,
> > I am currently using Python 2.7.1 on AIX 6.1 with cx_Oracle 5.0.4 and
> notice
> > that cursor.close() is NOT decrementing the open cursor count in Oracle.
> > The open cursor count is only decremented when the connection itself if
> > closed.
> > The connection object is created internally by the django framework and
> > hence I do not have control over closing it.
> > There are other applications running on the same AIX box against the same
> > Oracle version and they seem to be working fine - no open cursor leaks.
> > Can somebody point me in the right direction?
> > The versions of the software used is shown below :
> > AIX VERSION
> > $ oslevel -r
> > 6100-01
> > $ uname -a
> > AIX camanht1 1 6 00C70D354C00
> >
> > ORACLE VERSION
> > SQL*Plus: Release 11.1.0.6.0 - Production on Tue Feb 8 06:43:25 2011
> > Copyright (c) 1982, 2007, Oracle. All rights reserved.
> >
> > Connected to:
> > Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
> Production
> > With the Partitioning, OLAP, Data Mining and Real Application Testing
> > options
> > PYTHON AND CX_ORACLE VERSION
> > $ ./python
> > Python 2.7.1 (r271:86832, Feb 7 2011, 06:50:53)
> > [GCC 4.2.4] on aix6
> > Type "help", "copyright", "credits" or "license" for more information.
> >>>> import cx_Oracle
> >>>> cx_Oracle.version
> > '5.0.4'
> > COMMANDS TO REPRODUCE
> >>>> connection=cx_Oracle.connect("VANGIA/logfire%5678@CADEVMNH")
> >>>> cur=connection.cursor()
> >>>> cur.execute("select sysdate from dual") #### Number of open cursors
> >>>> is incremented
> > <__builtin__.OracleCursor on <cx_Oracle.Connection to VANGIA@CADEVMNH>>
> >>>> res=cur.fetchone()
> >>>> res[0]
> > datetime.datetime(2011, 2, 8, 6, 55, 38)
> >>>> cur.close() #### Number of open cursors is NOT decremented
> >>>> del cur #### Number of open cursors is NOT decremented
> >>>> del connection #### Opened cursors are released at this point
> >
> >
> > Thanks,
> > Vikrant Angia
> >
> ------------------------------------------------------------------------------
> > The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
> > Pinpoint memory and threading errors before they happen.
> > Find and fix more than 250 security defects in the development cycle.
> > Locate bottlenecks in serial and parallel code that limit performance.
> > http://p.sf.net/sfu/intel-dev2devfeb
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
> ------------------------------------------------------------------------------
> The ultimate all-in-one performance toolkit: Intel(R) Parallel Studio XE:
> Pinpoint memory and threading errors before they happen.
> Find and fix more than 250 security defects in the development cycle.
> Locate bottlenecks in serial and parallel code that limit performance.
> http://p.sf.net/sfu/intel-dev2devfeb
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X