LANGUAGE/!$%!% ERROR NOTE 2017. 5. 10. 13:29

!markdown


# 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 최대 수용가능 세션 수 확인

```sql

-- 사용중인 세션 확인

select * from v$resource_limit where resource_name = 'processes'

```


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

```sql

--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

```


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

```sql

-- 접속중인 세션 상세 확인

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으로 늘리는 예)

```sql

alter system set processes=300 scope=spfile

```


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

```sql

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

```

```sql

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

```

```sql

--(종료해야할 세션이 많을 때) INACTIVE인 모든 사용자 강제 종료 쿼리들을 만들어주는 쿼리.

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' FROM v$session where status = 'INACTIVE';

```


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

```bash

reboot now

```


## 4. Reference (참조)

- 오라클 커넥션수 구하는 쿼리좀 알려주세여:

[http://www.dator.co.kr/know_site/53083](http://www.dator.co.kr/know_site/53083)


- ORA-00020: maximum number of processes (%s) exceeded: 

[http://www.dba-oracle.com/t_ora_00020_maximum_processes_exceeded.htm](http://www.dba-oracle.com/t_ora_00020_maximum_processes_exceeded.htm)


- How to kill/terminate a session in Oracle: 

[http://web.synametrics.com/termorasession.htm](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](http://stackoverflow.com/questions/31201693/how-to-kill-all-active-and-inactive-oracle-sessions-for-user)