오라클 에러 정보 (ORA-01000) - maximum open cursors exceeded

Oracle DBMS 를 운영하다보면 자주 만나는 에러가 ORA-01000 입니다.

 

ORA-01000: maximum open cursors exceeded
ORA-01000: 최대 열기 커서 수를 초과했습니다

 

open cursor 의 갯수가 설정된 Max 값을 초과했다는 의미입니다.

설정된 open cursor 최대값은 sys 유저에서 show parameter open_cursors 커맨드로 조회해 볼 수 있습니다.

 

1000 으로 설정되어 있는데, 한 세션에서 1000개 이상 커서를 오픈하게 되면 ORA-01000 에러를 만납니다.

아래와 같이 테스트해서 상황을 재현해 볼 수 있습니다.

 

-- 테스트 프로시져 생성 CREATE OR REPLACE PROCEDURE proc_test1  AS     ii number;     v_cur integer; BEGIN          for ii in 1 .. 2000 loop         dbms_output.put('  ii => ' || ii);         v_cur := dbms_sql.open_cursor;         dbms_sql.parse(v_cur, 'select 1 from dual where 1 = ' || ii, dbms_sql.native);         --dbms_sql.close_cursor(v_cur);     end loop;     dbms_output.put_line('----------------------');   END; /   -- 테스트 프로시져 실행 set serveroutput on exec proc_test1;

 

위 프로시져는 2000번 루핑을 돌면서 커서를 Open 했다가 Close 하는 프로시져 입니다.

일부러, close_cursor() 호출하는 부분을 주석처리(--)해서 실행이 안되도록 했습니다.

이렇게 하면, 커서가 너무 많이 오픈되면서 에러가 발생하게 됩니다.

 

어떤 세션에서 커서를 많이 오픈한 건지 아래와 같은 SQL문으로 조회해 볼 수 있습니다.

 

 
select a.sid, s.process, s.machine, s.module, s.action, count(*) as cnt             
from V$OPEN_CURSOR a, V$SESSION s
where s.sid = a.sid
group by a.sid, s.process, s.machine, s.module, s.action
having count(*) > 100
order by count(*) desc;
 

 

DB 파라메타 open_cursors = 1000 을 넘는 것을 확인할 수 있습니다.

 

위에 나온 SID 를 이용해서 어떤 SQL 인지도 확인해 볼 수 있습니다.

 

 

주의할 것은 위 V$OPEN_CURSOR 뷰에 있는 정보는 ORA-01000 에러를 유발한 세션이 살아있는 동안만 남아있습니다.

ORA-01000 에러를 발생시킨 프로그램이 세션을 종료해버리면, 정보도 함께 사라집니다.

 

그리고, 한번 ORA-01000 에러가 발생한 세션은 해당 세션에서 다른 SQL이 실행되어도 계속 ORA-01000 에러가 발생합니다.

해당 세션은 아직 살아있고 Open 된 커서는 여전히 Close 되지 못한 상황이기 때문입니다.

 

위에서처럼 Dynamic SQL 을 사용하고, 프로그램에서 반복문(for/while)을 사용해서 커서가 오픈되는 경우에 주로 발생합니다.

가급적 Dynamic SQL 은 변수를 사용하는 Static SQL 로 바꿔주는 것이 좋습니다.

 

불가피한 경우, 반복문 횟수를 줄이거나, open_cursors 파라메타값을 늘려주면 에러가 덜 발생합니다.

 

SQL> alter system set open_cursors = 2000 scope=both;