Kept getting open cursors exceeded with this code

Question

This is the error I keep receiving

SQL Error: ORA-01000: maximum open cursors exceeded 01000. 00000 - "maximum open cursors exceeded"

This is the code I have been working on

DROP TABLE STUDENT; 

CREATE TABLE STUDENT 
(
   Student_ID NUMBER(5) NOT NULL ENABLE 
   ,F_NAME VARCHAR2(30) NOT NULL ENABLE 
   ,L_NAME VARCHAR2(30) NOT NULL ENABLE 
   ,HomePhone_Number NUMBER(10) 
   ,Home_Address     VARCHAR2(35) 
   ,City             VARCHAR2(35) 
   ,State            VARCHAR(2) 
   ,Zip_Code         NUMBER(5) 
   ,Spouse_FName     VARCHAR2(25) 
   ,Spouse_LName     VARCHAR2(25) 
   ,Enrolled_ID      NUMBER(10) 
   ,Academic_Advisor_ID NUMBER(5) 
   ,PRIMARY KEY (Student_ID) ENABLE 
); 

DROP TABLE FACULTY; 

CREATE TABLE FACULTY 
(
   Faculty_ID            NUMBER(5) NOT NULL ENABLE 
   ,F_Name               VARCHAR2(30) NOT NULL ENABLE 
   ,L_Name               VARCHAR2(30) NOT NULL ENABLE 
   ,OfficePhone_Numnber  NUMBER(10) 
   ,Office_Number        NUMBER(10) 
   ,PRIMARY KEY (Faculty_ID) ENABLE 
); 

DROP TABLE ADVISOR; 

CREATE TABLE ADVISOR 
(
   Student_ID  NUMBER(5), 
   Faculty_ID  NUMBER(5), 
   Advisor_TYPE VARCHAR2(20) 
); 

--INSERT VALUES INTO STUDENT 

INSERT INTO STUDENT VALUES 
(1,'Emily','Bronte',9542403843,'742 Evergreen Terrace','Springfield','IL',34567,'Jason','Bourne',1001); 

INSERT INTO STUDENT VALUES 
(2,'Alice','Walker',5481354795,'3584 NW 29th St','Mason','FL',17465,'Michael','Myers',1002); 

INSERT INTO STUDENT VALUES 
(3,'Kurt','Vonnegut',5481354796,'14 Blanchfield','Summer','FL',12444,'Jane','Vonnegut',1003); 

INSERT INTO STUDENT VALUES 
(4,'Isaac','Asimov',5481354743,'6754 N Riva Ridge Loop','Evans Mills','NY',13602,'April','Asimov',1004); 

INSERT INTO STUDENT VALUES 
(5,'Thomas','Elliot',5481354799,'6754 W Riva Ridge Loop','Evans Mills','NY',13602,'Sara','Elliot',1005); 

INSERT INTO STUDENT VALUES 
(6,'Louisa','Alcott',5481357117,'6754 S Riva Ridge Loop','Evans Mills','NY',13602,'Freddy','Alcott',1006); 

--INSERT VALUES INTO FACULTY 

INSERT INTO FACULTY VALUES(500,'Marth','Graham'); 
INSERT INTO FACULTY VALUES(501,'Mikhail','Baryshinokov'); 
INSERT INTO FACULTY VALUES(502,'Jerome', 'Robbins'); 
INSERT INTO FACULTY VALUES(503,'Arthur', 'Mitchell'); 
INSERT INTO FACULTY VALUES(504,'Kir','Jooss'); 

--INSERT VALUES INTO ADVISOR 

INSERT INTO ADVISOR VALUES( 1, 500, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 2, 501, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 3, 502, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 4, 503, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 5, 504, 'Academic Advisor'); 
INSERT INTO ADVISOR VALUES( 1, 502, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 2, 504, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 3, 503, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 4, 501, 'Thesis Advisor'); 
INSERT INTO ADVISOR VALUES( 5, 500, 'Thesis Advisor'); 

COMMIT; 

SET AUTOTRACE ON; 

--QUERY : All students under a specific advisor 

SELECT FACULTY.Faculty_ID,FACULTY.F_Name,FACULTY.L_Name,STUDENT.Student_ID,F_Name,L_Name 
FROM STUDENT,FACULTY,ADVISOR 
WHERE (STUDENT.STUDENT_ID = ADVISOR.STUDENT_ID) AND 
(FACULTY.Faculty_ID = ADVISOR.Faculty_ID) 
ORDER BY FACULTY.Faculty_ID; 

CREATE UNIQUE INDEX index_name 
ON ADVISOR(Student_ID,Faculty_ID); 

SET AUTOTRACE ON; 

--QUERY : All students under a specific advisor 

SELECT FACULTY.Faculty_ID,FACULTY.F_Name,FACULTY.L_Name,STUDENT.Student_ID,F_Name,L_Name 
FROM STUDENT,FACULTY,ADVISOR 
WHERE (STUDENT.Student_ID = ADVISOR.Student_ID) AND 
(FACULTY.Faculty_ID = ADVISOR.Faculty_ID) 
ORDER BY FACULTY.Faculty_ID;

After running this on run script in Oracle SQL developer this is the errors i get Error report - SQL Error: ORA-01000: maximum open cursors exceeded 01000. 00000 - "maximum open cursors exceeded" *Cause:
*Action: Unable to gather statistics please unsure user has correct access. The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

Does this answer your question? [java.sql.SQLException: - ORA-01000: maximum open cursors exceeded](https://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded) — Dale K, Mar 27 '20 at 01:51
Can you add some more context about the problem? What environment are you running this in? What is the result of `select value from v$parameter where name = 'open_cursors';` I'm surprised this code would throw a maximum open cursors error, since I would expect it to only use roughly one cursor. Look at this data dictionary view to check for any unexpected open cursors: `select * from gv$open_cursor order by sid;` — Jon Heller, Mar 27 '20 at 03:29
Exactly; there's nothing here that looks suspicious. Which part of code you posted raises the error? — Littlefoot, Mar 27 '20 at 06:34
Error starting at line : 1 in command - DROP TABLE STUDENT Error report - SQL Error: ORA-01000: maximum open cursors exceeded 01000. 00000 - "maximum open cursors exceeded" sqldeveloper — Steve, Mar 27 '20 at 13:26
@Steve If a simple DDL throws an open cursors error, that implies something is seriously wrong with your database. Can you run `select value from v$parameter where name = 'open_cursors';` - I wonder if someone lowered the number of something ridiculous like 1. Also, is that the *entire* error message, or is there a message stack? Possibly there is a database trigger that is failing, causing every DDL to fail. If you have a DBA, I would get them involved. If this is a test instance you manage yourself, I'd try restarting or just building a new database. — Jon Heller, Mar 28 '20 at 02:29
@JonHeller Thank you, there is an error stack I just couldn't post it here it was too many characters. Im using a virtual host from my school, the only reason I was worrying is because I have to submit my code via txt and my instructor has to run it on her end, I didn't want it to be wrong or with errors. — Steve, Mar 28 '20 at 17:01
I ran the code select value from v$parameter where name = 'open_cursors'; i get the same error message as before "maximum open cursors exceeded. — Steve, Mar 28 '20 at 17:08
@Steve Then you should tell your instructor; it looks like the virtual host is broken somehow. — Jon Heller, Mar 28 '20 at 22:03
@JonHeller Thank you and everyone else for all your help. Have a safe and good week. — Steve, Mar 30 '20 at 00:23

score 1 · Answer 1 · answered Mar 27 '20 at 01:32

1

Oracle can hold maximum 300 open_cursors by default.

A solution is to increase the open_cursors count limit. This can be done by the following sql code

alter system set open_cursors = 2000 scope=both;

This was asked before at this link: java.sql.SQLException: - ORA-01000: maximum open cursors exceeded and there the problem is much broader described.

answered Mar 27 '20 at 01:32

Mihai Lucian Rîtan

15
7

3

This might *avoid* the problem but it may not truly *solve* it. The posted code is so simple it shouldn't generate anywhere near the default number of open cursors. – Jon Heller Mar 27 '20 at 03:33
Iam new to sql i have been using oracle sql developer and when hit the run script button that is the error message i would recieve. I got this error for each table i created and the from and where part as well. – Steve Mar 27 '20 at 13:31

Kept getting open cursors exceeded with this code

1 Answers1