검색결과 리스트
글
!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)