Home » Developer & Programmer » JDeveloper, Java & XML » ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded [message #161215] Thu, 02 March 2006 12:12 Go to next message
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
Re: ORA-01000: maximum open cursors exceeded [message #161221 is a reply to message #161215] Thu, 02 March 2006 13:31 Go to previous messageGo to next message
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
Re: ORA-01000: maximum open cursors exceeded [message #161225 is a reply to message #161221] Thu, 02 March 2006 13:45 Go to previous messageGo to next message
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
Re: ORA-01000: maximum open cursors exceeded [message #161227 is a reply to message #161215] Thu, 02 March 2006 13:53 Go to previous messageGo to next message
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), then you have to wonder why you're hitting this at all....

Rgds

Re: ORA-01000: maximum open cursors exceeded [message #161232 is a reply to message #161227] Thu, 02 March 2006 14:35 Go to previous messageGo to next message
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
Re: ORA-01000: maximum open cursors exceeded [message #161248 is a reply to message #161215] Thu, 02 March 2006 16:28 Go to previous messageGo to next message
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
Re: ORA-01000: maximum open cursors exceeded [message #161253 is a reply to message #161248] Thu, 02 March 2006 16:58 Go to previous messageGo to next message
jance
Messages: 4
Registered: March 2006
Location: MA
Junior Member

I think I need to ask DBA to help.


Thanks.
Jance
Re: ORA-01000: maximum open cursors exceeded [message #354905 is a reply to message #161253] Tue, 21 October 2008 23:23 Go to previous messageGo to next message
vnmaradona
Messages: 4
Registered: October 2008
Location: Viet Nam
Junior Member

U should login by user sys with sysdba.
Re: ORA-01000: maximum open cursors exceeded [message #354908 is a reply to message #354905] Tue, 21 October 2008 23:29 Go to previous messageGo to next message
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;
Re: ORA-01000: maximum open cursors exceeded [message #354937 is a reply to message #354908] Wed, 22 October 2008 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: ORA-01000: maximum open cursors exceeded [message #354938 is a reply to message #354908] Wed, 22 October 2008 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01000: maximum open cursors exceeded [message #354958 is a reply to message #354938] Wed, 22 October 2008 01:26 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-01000: maximum open cursors exceeded [message #354975 is a reply to message #354958] Wed, 22 October 2008 01:58 Go to previous messageGo to next message
vnmaradona
Messages: 4
Registered: October 2008
Location: Viet Nam
Junior Member

I fixed it. This package is invalid.
Re: ORA-01000: maximum open cursors exceeded [message #354987 is a reply to message #354975] Wed, 22 October 2008 02:30 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Recompile it and if it is still invalid then there are other errors.
Query user_errors.

Regards
Michel
Previous Topic: xdb.jar JDK problem (urgent)
Next Topic: XML DB Comparison
Goto Forum:
  


Current Time: Sat Dec 10 04:54:17 CST 2016

Total time taken to generate the page: 0.13718 seconds