OraFAQ Forum: JDeveloper, Java & XML » ORA-01000: maximum open cursors exceeded

Home » Developer & Programmer » JDeveloper, Java & XML » ORA-01000: maximum open cursors exceeded

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01000: maximum open cursors exceeded [message #161215]

Thu, 02 March 2006 12:12

jance
Messages: 4
Registered: March 2006
Location: MA

Junior Member

I got the following error. I closed every statment. How do I set open cursor higher. I used Oracle 9.2. I cannot find init.ora file. Any suggestion will be very helpful.

Thank you very much,

Jance
---------------------------------------------------------------
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:626)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:182)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:500)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:1004)
at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:532)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1110)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2933)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:2974)
at sqlj.runtime.ExecutionContext$OracleContext.oracleExecuteQuery(ExecutionContext.java:1591)
at duplicates.Cleanup.hasHistory(Cleanup.sqlj:1320)
at duplicates.Listing.getType(Listing.java:188)
at duplicates.Cleanup.chooseGroupActions(Cleanup.sqlj:1029)
at duplicates.Cleanup.findBestAnchor(Cleanup.sqlj:999)
at duplicates.Cleanup.chooseGroupsActions(Cleanup.sqlj:987)
at duplicates.Cleanup.findAllDuplicatesByPhone(Cleanup.sqlj:370)
at duplicates.Cleanup.main(Cleanup.sqlj:1448)
20060301 java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #161221 is a reply to message #161215]

Thu, 02 March 2006 13:31

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

Well, you can set it by the open_cursors parameter, however, you have to ask yourself why you're hitting this problem at all? Are you correctly closing your cursors after you've used them?

See http://asktom.oracle.com/pls/ask/f?p=4950:8:9253786818282941511::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1041031921901
for a good discussion on this.

Rgds

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #161225 is a reply to message #161221]

Thu, 02 March 2006 13:45

jance
Messages: 4
Registered: March 2006
Location: MA

Junior Member

Thank you very much. I sure I closed the cursors after I used them. Do you know how to set the open_cursors? Where? Do Oracle 9.2 have init.ora file? Because I don't have the file.

Jance

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #161227 is a reply to message #161215]

Thu, 02 March 2006 13:53

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

What's your open_cursors set to now?

Go to SQL*Plus and type

SQL> show parameter cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
open_cursors                         integer     300
session_cached_cursors               integer     20

Oracle 9i CAN have an init.ora (PFILE), but most DBAs create SPFILEs which have certain advantages.
A way to see is if the spfile parameter is set, i.e.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\SPFILEORCL.ORA

You can modify the parameter via

SQL> ALTER SYSTEM SET open_cursors=1000;

System altered.

But, seriously, if your database has a relatively high open_cursors (i.e. it's not 5 or something! Wink

Wink

), then you have to wonder why you're hitting this at all....

Rgds

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #161232 is a reply to message #161227]

Thu, 02 March 2006 14:35

jance
Messages: 4
Registered: March 2006
Location: MA

Junior Member

Thank you very much. I used SQL Plus worksheet.

SQL>show parameter cursors, and show parameter spfile
it gave me the following answer.

ORA-00942: table or view does not exist

And I tried to set the maximum cursors, it gave me the
following message:

ALTER SYSTEM SET open_cursors=1000
*
ERROR at line 1:
ORA-01031: insufficient privileges

I don't know what do I do wrong. I am new to use Oracle.
Any suggestion will be very helpful.

Thanks again,

Jance

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #161248 is a reply to message #161215]

Thu, 02 March 2006 16:28

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

Yes, you'd need to login as a user with enough privileges to do this. Can you not ask your DBA to help you?

Rgds

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #161253 is a reply to message #161248]

Thu, 02 March 2006 16:58

jance
Messages: 4
Registered: March 2006
Location: MA

Junior Member

I think I need to ask DBA to help.

Thanks.
Jance

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354905 is a reply to message #161253]

Tue, 21 October 2008 23:23

vnmaradona
Messages: 4
Registered: October 2008
Location: Viet Nam

Junior Member

U should login by user sys with sysdba.

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354908 is a reply to message #354905]

Tue, 21 October 2008 23:29

vnmaradona
Messages: 4
Registered: October 2008
Location: Viet Nam

Junior Member

I ran this statement "ALTER SYSTEM SET open_cursors=1000;
" but i still have problem. In database i have package:
PACKAGE BODY test
AS

PROCEDURE PR_CREATE_CLOB ( p_clob OUT CLOB)
IS
BEGIN
DBMS_LOB.CREATETEMPORARY(P_CLOB, TRUE);
END;

END Test;

08/10/22 11:13:28 java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'PR_CREATE_CLOB' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

08/10/22 11:13:28 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
08/10/22 11:13:28 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
08/10/22 11:13:28 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
08/10/22 11:13:28 at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
08/10/22 11:13:28 at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184)
08/10/22 11:13:28 at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873)
08/10/22 11:13:28 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)
08/10/22 11:13:28 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3001)
08/10/22 11:13:28 at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3093)
08/10/22 11:13:28 at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4286)
08/10/22 11:13:28 at oracle.oc4j.sql.proxy.PreparedStatementBCELProxy.execute(PreparedStatementBCELProxy.java:333)
08/10/22 11:13:28 at com.iflex.fcc.web.ScriptsGeneratorServlet.toClob(ScriptsGeneratorServlet.java:150)
08/10/22 11:13:28 at com.iflex.fcc.web.ScriptsGeneratorServlet.processRequest(ScriptsGeneratorServlet.java:85)
08/10/22 11:13:28 at com.iflex.fcc.web.ScriptsGeneratorServlet.doPost(ScriptsGeneratorServlet.java:54)
08/10/22 11:13:28 at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
08/10/22 11:13:28 at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
08/10/22 11:13:28 at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:713)
08/10/22 11:13:28 at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
08/10/22 11:13:28 at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
08/10/22 11:13:28 at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
08/10/22 11:13:28 at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:221)
08/10/22 11:13:28 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:122)
08/10/22 11:13:28 at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:111)
08/10/22 11:13:28 at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
08/10/22 11:13:28 at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:239)
08/10/22 11:13:28 at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:34)
08/10/22 11:13:28 at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:880)
08/10/22 11:13:28 at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
08/10/22 11:13:28 at java.lang.Thread.run(Thread.java:595)

In database i have package:
PACKAGE BODY test
AS

-------------------------------------------------------------------------------

PROCEDURE PR_CREATE_CLOB ( p_clob OUT CLOB)
IS
BEGIN
DBMS_LOB.CREATETEMPORARY(P_CLOB, TRUE);
END;

-------------------------------------------------------------------------------

END Test;

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354937 is a reply to message #354908]

Wed, 22 October 2008 00:38

Frank
Messages: 7901
Registered: March 2000

Senior Member

How does this relate to the max open cursors problem?!
How do you call your packaged procedure? You have to prefix it with the package name.

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354938 is a reply to message #354908]

Wed, 22 October 2008 00:38

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is the relation bewteen:
ORA-01000: maximum open cursors exceeded
and
PLS-00201: identifier 'PR_CREATE_CLOB' must be declared

You can increase open_cursor to 100000000000, you will still have the same error.

Regards
Michel

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354958 is a reply to message #354938]

Wed, 22 October 2008 01:26

vnmaradona
Messages: 4
Registered: October 2008
Location: Viet Nam

Junior Member

How i can fix it?

[Updated on: Wed, 22 October 2008 01:28]

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354972 is a reply to message #354958]

Wed, 22 October 2008 01:47

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is your code and objects, I don't know nor have them, only you can answer the reason of the error.
All what you can say is that Oracle is unable to find an object named 'PR_CREATE_CLOB' in the scope it searches it.

Regards
Michel

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354975 is a reply to message #354958]

Wed, 22 October 2008 01:58

vnmaradona
Messages: 4
Registered: October 2008
Location: Viet Nam

Junior Member

I fixed it. This package is invalid.

Report message to a moderator

Re: ORA-01000: maximum open cursors exceeded [message #354987 is a reply to message #354975]

Wed, 22 October 2008 02:30

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Recompile it and if it is still invalid then there are other errors.
Query user_errors.

Regards
Michel

Report message to a moderator

Previous Topic:	xdb.jar JDK problem (urgent)
Next Topic:	XML DB Comparison

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 00:57:23 CST 2024