\uc5bc\ub9c8\uc804 \ud504\ub85c\uc81d\uc5d0\uc11c \ucee4\ub125\uc158 \ub9ad\uc73c\ub85c \uc778\ud574 \ub9ce\uc740 \uc7a5\uc560\uac00 \uc788\uc5c8\uc2b5\ub2c8\ub2e4. <\/p>\n
\uc544\ub798 \ub0b4\uc6a9\uc774 \ub3c4\uc6c0\uc774 \ub420 \uc218 \uc788\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n
===================================================================================
\ucd5c\uadfc\ubb38\uc11c\uc218\uc815\uc77c\uc790: 2001.10.31<\/p>\n
1. JDBC\ub85c \ub3d9\uc2dc\uc5d0 \uc5f0\uacb0\ud560 \uc218 \uc788\ub294 java.sql.Connection \uc218<\/p>\n
E:\\temp> type DbMaxConnTest.java
import java.util.*;
import java.sql.*;
public class DbMaxConnTest
{
public static int MAX=10000;
public static void main(String[] args) throws Exception {
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Vector conns = new Vector();
try {
for(int i=0 ; i< MAX; i++) {
Connection conn = DriverManager.getConnection(
“jdbc:oracle:thin:@localhost:1521:ORA8i”,
“scott”, “tiger”);
conns.addElement(conn);
System.out.println((i+1) + “-th connected”);
}
}
catch(Exception e){
e.printStackTrace();
}
finally {
Enumeration enum = conns.elements();
while(enum.hasMoreElements()){
Connection conn = (Connection)enum.nextElement();
try { conn.close();}catch(Exception e){}
}
}
}
}<\/p>\n
E:\\temp>set classpath=.;c:\\jdk1.1.8\\lib\\classes.zip;e:\\downloads\\jdbc\\classe
s111_01.zip<\/p>\n
E:\\temp>javac DbMaxConnTest.java<\/p>\n
E:\\temp>java DbMaxConnTest
1-th connected
2-th connected
3-th connected
……
44-th connected
45-th connected
46-th connected
47-th connected
java.sql.SQLException: ORA-00020: maximum number of processes (59) exceeded<\/p>\n
at java.lang.Throwable.<init>(Throwable.java:74)
at java.lang.Exception.<init>(Exception.java:38)
at java.sql.SQLException.<init>(SQLException.java:36)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
at oracle.jdbc.ttc7.O3log.receive1st(O3log.java)
at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
at java.sql.DriverManager.getConnection(Compiled Code)
at java.sql.DriverManager.getConnection(DriverManager.java:119)
at DbMaxConnTest.main(Compiled Code)<\/p>\n
1.1 Oracle<\/p>\n
Oracle \uc5d0\uc11c \uc704\uc5d0\uc11c \ucc98\ub7fc \ucd5c\ub300\ub85c \ub3d9\uc2dc\uc5d0 Open \ud560 \uc218 \uc788\ub294 java.sql.Connection \uc218\ub294
(\uc800\uc758 NT\uc6a9 Personal Oracle 8.1.5 \uc758 \uacbd\uc6b0) E:\\Oracle\\ADMIN\\ORA8i\\pfile\\init.ora
\uc5d0\uc11c \uc544\ub798\uc758 \ud30c\ub77c\uba54\ud130\ub97c \uc218\uc815\ud568\uc73c\ub85c\uc368 \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/p>\n
………
processes = 59 # INITIAL
# processes = 50 # SMALL
# processes = 100 # MEDIUM
# processes = 200 # LARGE
………<\/p>\n
\uc774 \ud30c\uc77c\uc740 \ud1b5\uc0c1 init<SID>.ora \ud30c\uc77c\uc774\ub77c\ub294 SID \uad6c\uc131\ud30c\uc77c\uc5d0\uc11c \uc218\uc815\ud569\ub2c8\ub2e4.<\/p>\n
NOTE: \uc65c Max\uac12\uc740 59 \uc778\ub370, 47\uae4c\uc9c0\ub9cc \uc5f0\uacb0\ub418\ub294 \uac83\uc778\uc9c0\ub294 \uc800\ub450 \ubaa8\ub974\uaca0\uc2b5\ub2c8\ub2e4. \uc218\uce58\ub97c
\uc62c\ub824\ub3c4 \uaf2d 12\uac1c\uc529\uc758 \ucc28\uc774\uac00 \uc0dd\uae30\ub294 \uad70\uc694…
—————————————
\uc81c\ubaa9 Re: JSN:Connection\/Statement \ucd5c\ub300 \ub3d9\uc2dc Open \uc218
\ubcf4\ub0b8\ub0a0\uc9dc Mon, 23 Oct 2000 20:56:55 KST
\ubcf4\ub0b8\uc774 “CHOULGU KANG” <cgkang@lgeds.lg.co.kr>
\ubc1b\ub294\uc774 “javaservice@hanmail.net” <javaservice@hanmail.net> <\/p>\n
INIT\ud30c\uc77c\uc5d0 \uc815\uc758\ud574 \ub193\uc740 DB \ud504\ub85c\uc138\uc2a4\uc218\ub97c \uc804\uccb4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub294 \uc774\uc720\ub294 \uc624\ub77c\ud074
\ubc31\uadf8\ub77c\uc6b4\ub4dc \ud504\ub85c\uc138\uc2a4 \ub4f1\uc774 \uc0ac\uc6a9\ud558\ub294 \uc218\ub54c\ubb38\uc785\ub2c8\ub2e4.
\uc88b\uc740 \ud558\ub8e8 \ub418\uc2dc\uae38 \ubc14\ub78d\ub2c8\ub2e4. \uac15\ucca0\uad6c.
—————————————<\/p>\n
1.2 IBM UDB DB2<\/p>\n
\ub300\ubd80\ubd84\uc758 DB\uac00 \uc774\uc640 \uc720\uc0ac\ud55c \ud30c\ub77c\uba54\ud130\ub97c \uc81c\uacf5\ud560 \uac83\uc785\ub2c8\ub2e4.
\uc608\ub97c \ub4e4\uc5b4 IBM UDB DB2 \uc758 \uacbd\uc6b0\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \ubc29\uc2dd\uc73c\ub85c \uc774 \uc218\uce58\ub97c \ubc14\uafc0 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
# su – db2inst1
# db2 get dbm cfg | grep MAXAGENTS
\uae30\uc874 \uc5d0\uc774\uc804\ud2b8\uc758 \ucd5c\ub300 \uc218 (MAXAGENTS) = 200
# db2 update dbm cfg using maxagents <number><\/p>\n
# db2 get db cfg for <database_name> | grep MAXAPPLS
\uc2e4\ud589 \uc911\uc778 \ud504\ub85c\uadf8\ub7a8\uc758 \ucd5c\ub300 \uc218 (MAXAPPLS) = 100
# db2 update db cfg for <database_name> using maxappls <mumber><\/p>\n
2. \ud558\ub098\uc758 Connection \uc5d0\uc11c \ub3d9\uc2dc\uc5d0 \uc0dd\uc131\ub420 \uc218 \uc788\ub294 Statement \uc758 \uc218<\/p>\n
E:\\temp> type DbMaxStmtTest.java
import java.util.*;
import java.sql.*;
public class DbMaxStmtTest
{
public static int MAX=10000;
public static void main(String[] args) throws Exception {
Class.forName(“oracle.jdbc.driver.OracleDriver”);
Connection conn = DriverManager.getConnection(
“jdbc:oracle:thin:@localhost:1521:ORA8i”,
“scott”, “tiger”);
Vector stmts = new Vector();
try {
for(int i=0 ; i< MAX; i++) {
Statement stmt = conn.createStatement();
stmts.addElement(stmt);
System.out.println((i+1) + “-th statement created”);
}
}
catch(Exception e){
e.printStackTrace();
}
finally {
Enumeration enum = stmts.elements();
while(enum.hasMoreElements()){
Statement stmt = (Statement)enum.nextElement();
try { stmt.close();}catch(Exception e){}
}
try { conn.close();}catch(Exception e){}
}
}
}<\/p>\n
E:\\temp> javac DbMaxStmtTest.java<\/p>\n
E:\\temp> java DbMaxStmtTest
1-th statement created
2-th statement created
3-th statement created
….
48-th statement created
49-th statement created
50-th statement created
java.sql.SQLException: ORA-01000: \ucd5c\ub300 \uc5f4\uae30 \ucee4\uc11c \uc218\ub97c \ucd08\uacfc\ud588\uc2b5\ub2c8\ub2e4
(maximum open cursors exceeded)<\/p>\n
at java.lang.Throwable.<init>(Throwable.java:74)
at java.lang.Exception.<init>(Exception.java:38)
at java.sql.SQLException.<init>(SQLException.java:36)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
at oracle.jdbc.ttc7.Oopen.receive(Compiled Code)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java)
at oracle.jdbc.driver.OracleConnection.createStatement(OracleConnection.
java)
at DbMaxStmtTest.main(Compiled Code)<\/p>\n
2.1 Oracle<\/p>\n
\uc774 \ucc98\ub7fc, \ud55c java.sql.Connection \uc5d0\uc11c \ub3d9\uc2dc\uc5d0 \uc0dd\uc131\ud560 \uc218 \uc788\ub294 statement \uc758 \uc218\ub294
Oracle\uc758 \uacbd\uc6b0 Oracle SID \uad6c\uc131\ud30c\uc77c\uc5d0\uc11c \uc544\ub798\uc758 \ud30c\ub77c\uba54\ud130\ub97c \ucd94\uac00\ub85c \ub123\uc5b4\uc90c\uc73c\ub85c\uc368
\ubcc0\uacbd\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
…..
# OPEN_CURSORS=50 # Default
OPEN_CURSORS=100
…..<\/p>\n
NOTE:
Error Message: “ORA-01000: maximum open cursors exceeded”
The number of cursors one client can open at a time on a connection is limited
(50 is the default value). You do need to explicitly close the statement, by
using the method stmt.close() in order to close and freeup the cursors. <\/p>\n
If you dont close these cursors explicitly, you will get this error eventually.
Simply increasing the “OPEN_CURSORS” limit can help you avoid the problem for
a while, but that just hides the problem, not solve it. It is your responsibilty
to explicitly close out cursors that you no longer need. <\/p>\n
NOTE: 2001.10.31
Oracle \uc758 \uacbd\uc6b0, \ud604\uc7ac \uc5f4\ub824\uc9c4 \ucd1d Statement\uc758 \uac1c\uc218\ub97c \ubcf4\ub294 \ubc29\ubc95\uc774 \uc788\uc2b5\ub2c8\ub2e4.
——————————————-
select sid, count(*) cnt from v$open_cursor
where user_name = ‘username’
group by sid
order by cnt desc
——————————————-
(\ub3c4\uc6c0\uc8fc\uc2e0\ubd84: \uc815\ud5cc\uc2dd <vblove@buttle.co.kr>)<\/p>\n
2.2 IBM UDB DB2<\/p>\n
IBM UDB DB2 \uc758 \uacbd\uc6b0\ub294 Oracle \uc758 “open_cursors” \uc640 \uac19\uc740 \uac1c\ub150\uc758 \ud30c\ub77c\uba54\ud130\uac00
\uc5c6\uc2b5\ub2c8\ub2e4. \uc2dc\uc2a4\ud15c\uc758 \uba54\ubaa8\ub9ac\uac00 \ud5c8\uc6a9\ud558\ub294 \ud55c \ub05d\uae4c\uc9c0 \uc8fd\uc744 \ub54c \uae4c\uc9c0 \uc0dd\uc131\uc2dc\ud0a4\ub294
\uad70\uc694…<\/p>\n
\uc544\ub798\uc640 \uac19\uc740 \ud14c\uc2a4\ud2b8\uc5d0\uc11c 1463 \uac1c\uc758 statement \ub97c \ub9cc\ub4e4\ub354\ub2c8, \uc2dc\uc2a4\ud15c\uc774 \ubc85\ubc85\ub290\ub824\uc9c0\uace0
\uae09\uae30\uc57c \uc5d0\ub7ec\ub97c \ub0b4\uace0 \ub9c8\ub294 \uad70\uc694…..
\uc2dc\uc2a4\ud15c\uc758 \uba54\ubaa8\ub9ac\uc5d0 \ub530\ub77c \uac1c\uc218\uc81c\ud55c\uc774 \uc788\uc744 \ubfd0\uc778 \ub4ef \ud569\ub2c8\ub2e4.<\/p>\n
E:\\temp> type Db2MaxStmtTest.java
import java.util.*;
import java.sql.*;
public class Db2MaxStmtTest
{
public static int MAX=10000;
public static void main(String[] args) throws Exception {
Class.forName(“COM.ibm.db2.jdbc.app.DB2Driver”).newInstance();
Connection conn = DriverManager.getConnection(
“jdbc:db2:was”, “db2admin”, “db2admin”);
Vector stmts = new Vector();
try {
for(int i=0 ; i< MAX; i++) {
Statement stmt = conn.createStatement();
stmts.addElement(stmt);
System.out.println((i+1) + “-th statement created”);
}
}
catch(Exception e){
e.printStackTrace();
}
finally {
Enumeration enum = stmts.elements();
while(enum.hasMoreElements()){
Statement stmt = (Statement)enum.nextElement();
try { stmt.close();}catch(Exception e){}
}
try { conn.close();}catch(Exception e){}
}
}
}<\/p>\n
E:\\temp> javac Db2MaxStmtTest.java<\/p>\n
E:\\temp> java Db2MaxStmtTest
1-th statement created
2-th statement created
3-th statement created
….
1461-th statement created
1462-th statement created
1463-th statement created
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC \ub4dc\ub77c\uc774\ubc84] CLI0601E \uc720\ud6a8\ud558\uc9c0 \uc54a\uc740 \uba85\ub839
\ubb38 \ud578\ub4e4 \ub610\ub294 \uba85\ub839\ubb38\uc774 \ub2eb\ud614\uc2b5\ub2c8\ub2e4. SQLSTATE=S1000
at java.lang.Throwable.<init>(Throwable.java:74)
at java.lang.Exception.<init>(Exception.java:38)
at java.sql.SQLException.<init>(SQLException.java:36)
at COM.ibm.db2.jdbc.DB2Exception.<init>(DB2Exception.java:93)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExce
ptionGenerator.java:278)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExce
ptionGenerator.java:187)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExcep
tionGenerator.java:426)
at COM.ibm.db2.jdbc.app.DB2Statement.<init>(Compiled Code)
at COM.ibm.db2.jdbc.app.DB2Statement.<init>(Compiled Code)
at COM.ibm.db2.jdbc.app.DB2Connection.createStatement(Compiled Code)
at Db2MaxStmtTest.main(Compiled Code)<\/p>\n
\uc704 \uc5d0\ub7ec\ub97c \ud639 \ubcf4\uc2e0 \uc801 \uc788\ub098\uc694? \uc5c9\ub6b1\ud55c \ub2e4\ub978 \ubd80\ubd84\uc744 \uc758\uc2ec\ud558\uc9c0 \uc54a\uc73c\uc168\ub358\uac00\uc694?
\ub9cc\uc57d, \uc5b4\ud50c\ub9ac\ucf00\uc774\uc158 \uc11c\ubc84 \ud504\ub85c\uc138\uc2a4\uac00 \uc810\uc720\ud558\uace0 \uc788\ub294 \uba54\ubaa8\ub9ac\uc0ac\uc774\uc988\uac00 \uacc4\uc18d\uc801\uc73c\ub85c
\uc99d\uac00\ud558\uba74\uc11c, \uae09\uae30\uc57c \uc704\uc640 \uac19\uc740 \uc5d0\ub7ec\ub97c \ub0b4\ub294 \uacbd\uc6b0\ub97c \ubcf4\uc2e0 \uc801 \uc788\ub098\uc694?<\/p>\n
DB2 \uc5d0\uc11c\ub294 Oracle \ucc98\ub7fc “ORA-01000: maximum open cursors exceeded” \ub77c\ub294
SQLException \uba54\uc138\uc9c0\uac00 \uc5c6\uc2b5\ub2c8\ub2e4. \ub610\ud55c \uc81c\ud55c\uc774 \uc5c6\uae30 \ub54c\ubb38\uc5d0, \ubb38\uc81c\ub97c \uc57c\uae30\ud558\uc5ec
\uc2dc\uc2a4\ud15c\uc774 \ub2e4\uc6b4\ub418\uae30 \uc804\uae4c\uc9c4 \uc544\ubb34\ub3c4 \uad00\uc2ec\ub3c4 \uac00\uc838\uc8fc\uc9c0 \uc54a\uace0, \uc124\ub839 \uba54\ubaa8\ub9ac \ubb38\uc81c\ub85c
\uc778\ud574 \ub2e4\uc6b4\ub418\uba74 \uc5c9\ub6b1\ud55c \uacf3\uc744 \uc758\uc2ec\ud558\uac8c \ub429\ub2c8\ub2e4.<\/p>\n
\uc774\uc81c \uc704\uc758 \uc5d0\ub7ec\uba54\uc138\uc9c0\ub97c \ub9cc\ub098\uba74, java.sql.Statement \ub97c \ubc18\ub4dc\uc2dc finally \uc808\uc5d0\uc11c \uaf2d
close() \ud558\uc138\uc694….<\/p>\n
PS: \uc774 \uae00\uc744 \uc77d\uc73c\uba74\uc11c “\uadf8\ub798\uc11c \uadf8\uac8c \uc5b4\uca0c\ub2e4\uad6c.. \uc2dc\ud070\ub465…” \ud558\uace0 \uacc4\uc2e0\ub2e4\uba74,
\uc544\uc9c1 \uc544\ub798\uae00\uc744 \ud0d0\ub3c5\ud558\uc9c0 \uc54a\uc73c\uc2e0\uac8c \ubd84\uba85\ud569\ub2c8\ub2e4.
“\uc11c\ube14\ub81b + JDBC \uc5f0\ub3d9\uc2dc \ucf54\ub529 \uace0\ub824\uc0ac\ud56d -\uc81c1\ud0c4-“
http:\/\/www.javaservice.net\/~java\/bbs\/read.cgi?m=devtip&b=servlet&c=r_p&n=968185187
<\/p>\n
——————————————————-
\ubcf8 \ubb38\uc11c\ub294 \uc790\uc720\ub86d\uac8c \ubc30\ud3ec\/\ubcf5\uc0ac \ud560 \uc218 \uc788\uc73c\ub098 \ubc18\ub4dc\uc2dc
\uc774 \ubb38\uc11c\uc758 \uc800\uc790\uc5d0 \ub300\ud55c \uc5b8\uae09\uc744 \uc0ad\uc81c\ud558\uc2dc\uba74 \uc548\ub429\ub2c8\ub2e4
================================================
\uc790\ubc14\uc11c\ube44\uc2a4\ub137 \uc774\uc6d0\uc601
E-mail: javaservice@hanmail.net
PCS:019-310-7324
================================================<\/p>\n","protected":false},"excerpt":{"rendered":"
\uc5bc\ub9c8\uc804 \ud504\ub85c\uc81d\uc5d0\uc11c \ucee4\ub125\uc158 \ub9ad\uc73c\ub85c \uc778\ud574 \ub9ce\uc740 \uc7a5\uc560\uac00 \uc788\uc5c8\uc2b5\ub2c8\ub2e4. \uc544\ub798 \ub0b4\uc6a9\uc774 \ub3c4\uc6c0\uc774 \ub420 \uc218 \uc788\uaca0\uc2b5\ub2c8\ub2e4. ===================================================================================\ucd5c\uadfc\ubb38\uc11c\uc218\uc815\uc77c\uc790: 2001.10.31 1. JDBC\ub85c \ub3d9\uc2dc\uc5d0 \uc5f0\uacb0\ud560 \uc218 \uc788\ub294 java.sql.Connection \uc218 E:\\temp> type DbMaxConnTest.javaimport java.util.*;import java.sql.*;public class DbMaxConnTest{ public static int MAX=10000; public static void main(String[] args) throws Exception { Class.forName(“oracle.jdbc.driver.OracleDriver”); Vector conns = […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[163],"tags":[1105,1107,115,1108],"class_list":["post-1008","post","type-post","status-publish","format-standard","hentry","category-about-knowledge-programs_java","tag-jdbc","tag-pool","tag-115","tag-1108"],"_links":{"self":[{"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/posts\/1008","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/comments?post=1008"}],"version-history":[{"count":0,"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/posts\/1008\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/media?parent=1008"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/categories?post=1008"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.msinterdev.org\/wp\/wp-json\/wp\/v2\/tags?post=1008"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}