java.sql.SQLException: ORA-01000: 최대 열기 커서 수를 초과했습니다

JDBC 코딩시 '최대열기 커서수를 초과했습니다'

java.sql.SQLException: ORA-01000: 최대 열기 커서 수를 초과했습니다
(maximum open cursors exceeded)


가끔 JDBC프로그램시 위의 에러가 발생하는 경우를 경험했습니다.

위 에러는 PreparedStatement나 Statement를 사용하고 close()를 하지 않아서 ..
오라클 프로세스당 커서수가 증가되어서 발생하는 에러입니다.

보통 위에 ORA-01000 에러가 발생하면 사용하는 트랜잭션 테이블에 Lock이 걸릴 확률이 높습니다.

오라클 입장에서 ORA-01000 에러를 모니터링 하는 방법을 소개 할려고 합니다.



# 오라클 프로세스당 커서수를 아래와 같이 확인해 볼 수 있습니다.

SELECT sid, count(sid) "cursor"
FROM V$OPEN_CURSOR
WHERE user_name = 'SCOTT'
GROUP BY sid
ORDER BY cnt DESC

SID cursor
---------- ----------
70 379
42 307
67 172
88 19
48 15
46 7
87 7
18 6
60 6
86 6
77 6



트랜잭션이나 동시 접속자에 따라 많이 틀리지만 오라클 cursor수가 100개가 넘는 세션에서
사용하고 있는 SQL문장은 의심을 해봐야 합니다.
(일반적인 사이트에서는 20개만 넘어도 의심해 봐야 합니다.)
그 SQL문장을 찾아서 JAVA에서 JDBC를 이용해서 코딩한 부분을 찾아 보면
PreparedStatement를 사용하고 close()를 해주지 않는 부분이 있습니다..


아래의 SQL문으로 현재 실행중인 SQL문이 사용하는 커서를 확인해 볼 수 있습니다.


# SQL문당 사용하는 커서수

SELECT sql_text, count(sid) cnt
FROM v$OPEN_CURSOR
GROUP BY sql_text
ORDER BY cnt DESC

SQL_TEXT CNT
------------------------------------------------------------ ----------
INSERT INTO ACCEPTANCELOG ( ACCEPTYEAR , ACCEPTL 90
SELECT INSTR(:b3, :b2,1, :b1) FROM DUAL 9
SELECT INSTR(:b3, :b2,1, :b1+1) FROM DUAL 9
SELECT companionname FROM accept_companion WHERE acceptyea 9
select count(*) seq from companion where passid = :1 an 7
SELECT a.appealname,a.appealpin, a.passid, a.passseq, 7


이렇게 커서의 수가 많은 첫번째 SQL문을 JAVA에서 코딩한 부분을 찾아 보면
PreparedStatement가 close()되지 않았을 확률이 높습니다.
특히 for문이나 while문등의 Loop문에서 PreparedStatement를 사용하는 부분을 유심히 봐야 합니다.




# 오라클 프로세스의 정보는 아래의 SQL문으로 확인해 볼 수 있습니다.
SELECT /*+ rule */
s.status "Status", s.serial# "Serial#", s.TYPE "Type",
s.username "DB User", s.osuser "Client User", s.server "Server",
s.machine "Machine", s.module "Module", s.terminal "Terminal",
s.program "Program", p.program "O.S. Program",
s.logon_time "Connect Time", lockwait "Lock Wait",
si.physical_reads "Physical Reads", si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes", s.process "Process",
p.spid, p.pid, s.serial#, si.sid, s.sql_address "Address",
s.sql_hash_value "Sql Hash", s.action
FROM v$session s, v$process p, sys.v_$sess_io si
WHERE s.paddr = p.addr(+)
AND si.sid(+) = s.sid
AND s.username IS NOT NULL
AND NVL (s.osuser, 'x') <> 'SYSTEM'
AND s.TYPE <> 'BACKGROUND'
ORDER BY 3





# '오라클 프로세스 초과 에러'
java.sql.SQLException: ORA-00020: maximum number of processes (100)


이 에러는 JDBC를 이용해서 오라클과 연결을 할 때 초보자들이 흔히 범할수 있는
오류중에 하나 입니다.

이 에러가 발생하는 원인은 JDBC의 Connection을 사용하고 close()를 해주지 않아서 발생을 합니다.

close를 해주지 않아 오라클 프로세스가 계속 증가하다가 결국엔 init.ora파일의 processes파라미터에서
정한 프로세스 개수를 초과해서 발생하게 됩니다.

Oracle에서 동시에 최대로 Open할 수 있는 java.sql.Connection 수는
$ORACLE_HOME/pfile/init.ora 에서 processes 파라미터에서 지정을 합니다.

processes=150



에러가 발생하면 processes의 프로세스 수를 증가시키는 것보다는
JAVA소스에서 Connection을 사용하는 부분에서 close()를 했는지 확인해 봐야 합니다.



출처: http://blog.naver.com/celestialorb/40004686084