Oracle Session

Oracle에 동시에 접속할 수 있는 Session의 수는 한정되어있다. Connect만 하고 Close를 하지 않는다면, 한계치에 다다랐을때 다음과 같은 에러가 발생한다.

1. Error (에러)

• ORA-12519

java.lang.RuntimeException: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

• ORA-00020

ORA-00020: maximum number of processes (150) exceeded

2. Check (확인)

• 접속중인 세션 and 최대 수용가능 세션 수 확인

-- 사용중인 세션 확인
select * from v$resource_limit where resource_name = 'processes'

• 접속중인 세션 확인 (DBUSER별)

--DB USER별 사용중인 세션 확인
(
        SELECT   '(ALL)' AS STATUS, '(ALL)' AS DBUSER, CURRENT_UTILIZATION AS ACCUMCOUNT,''||LIMIT_VALUE AS MAX_LIMIT
        FROM V$RESOURCE_LIMIT 
        WHERE RESOURCE_NAME = 'processes'
)
UNION
SELECT   STATUS, DBUSER, COUNT(DBUSER) AS ACCUMCOUNT, (select limit from dba_profiles where profile = 'DEFAULT' and RESOURCE_NAME = 'SESSIONS_PER_USER') AS MAX_LIMIT
FROM (
        SELECT S.STATUS AS STATUS, S.USERNAME AS DBUSER 
        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 1
)
GROUP BY STATUS, DBUSER
ORDER BY ACCUMCOUNT DESC

• 접속중인 세션 확인 (상세)

-- 접속중인 세션 상세 확인
select status1, type1, machine, module, dbuser, ClientUser, count(ClientUser) as accumCount
from (
        SELECT
                 s.status as status1, s.serial# "Serial#", s.TYPE as Type1,
                 s.username as DBUser, s.osuser as ClientUser, s.server "Server",
                 s.machine as Machine, s.module as Module, s.client_info "Client Info",
                 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, si.sid, s.audsid, 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 1
)
group by status1, type1, machine, module, dbuser, ClientUser
order by accumCount desc

3. Solution (해결책)

• 방법1: 세션 한계치를 늘린다.  (최대수용 세션수를 300으로 늘리는 예)

alter system set processes=300 scope=spfile

• 방법2: Session을 강제종료시킨다.

SELECT s.sid, serial#,  osuser,  program FROM v$session s;

ALTER SYSTEM KILL SESSION '{SID},{SERIAL#}' IMMEDIATE 

--(종료해야할 세션이 많을 때) INACTIVE인 모든 사용자 강제 종료 쿼리들을 만들어주는 쿼리.
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' FROM v$session where status = 'INACTIVE';

• 방법4: 컴퓨터 재부팅(reboot)

reboot now

4. Reference (참조)

• 오라클 커넥션수 구하는 쿼리좀 알려주세여: http://www.dator.co.kr/knowsite/53083

• ORA-00020: maximum number of processes (%s) exceeded:  http://www.dba-oracle.com/tora00020maximumprocessesexceeded.htm

• How to kill/terminate a session in Oracle:  http://web.synametrics.com/termorasession.htm

• How to kill all active and inactive oracle sessions for user: http://stackoverflow.com/questions/31201693/how-to-kill-all-active-and-inactive-oracle-sessions-for-user