16 03 2020

Oracle Lock 제거 및 Connection Pool 관련

1.DB Conneciton Pool 관련
Pool조회
select * from v$resource_limit where resource_name in ('processes', 'sessions');

Sessions 접속 정보 쿼리
SELECT username, program FROM v$session;

접속 사용자 제거

--alter system kill session 'session_id,serial#';
alter system kill session '32,11234';

1 2	--alter system kill session 'session_id,serial#'; alter system kill session '32,11234';

현재 Connection Pool 사용현황

SELECT  * 
  FROM  V$resource_limit 
 WHERE   resource_name IN ('processes','sessions');

SELECT *

FROM V$resource_limit

WHERE resource_name IN ('processes','sessions');

접속이 안되는 경우 INACTIVE 갯수가 많을 경우 삭제 하면 됨

SELECT  'alter system kill session ''' || sid || ',' || serial# || ''' immediate;'
  FROM  v$session
 WHERE  username = 'NURIWARE'
   AND  status = 'INACTIVE'

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''' immediate;'

FROM v$session

WHERE username = 'NURIWARE'

AND status = 'INACTIVE'

나오는 결과를 아래처럼 실행

alter system kill session '13,46763';
alter system kill session '29,30175';
alter system kill session '39,21235';
alter system kill session '43,53103';
alter system kill session '45,40359';
alter system kill session '46,50781';
alter system kill session '62,65397';
alter system kill session '71,15497';
alter system kill session '256,13051';
alter system kill session '262,14689';

alter system kill session '13,46763';

alter system kill session '29,30175';

alter system kill session '39,21235';

alter system kill session '43,53103';

alter system kill session '45,40359';

alter system kill session '46,50781';

alter system kill session '62,65397';

alter system kill session '71,15497';

alter system kill session '256,13051';

alter system kill session '262,14689';

2.DB Lock 관련

lock 조회

SELECT  DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER,

        VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE

FROM    V$LOCKED_OBJECT VO, DBA_OBJECTS DO

WHERE   VO.OBJECT_ID = DO.OBJECT_ID;

SELECT DO.OBJECT_NAME, DO.OWNER, DO.OBJECT_TYPE, DO.OWNER,

VO.XIDUSN, VO.SESSION_ID, VO.LOCKED_MODE

FROM V$LOCKED_OBJECT VO, DBA_OBJECTS DO

WHERE VO.OBJECT_ID = DO.OBJECT_ID;

lock 세부 조회

SELECT A.SID,   A.SERIAL#

  FROM V$SESSION A,  V$LOCK B,

       DBA_OBJECTS C

 WHERE A.SID = B.SID

   AND B.ID1 = C.OBJECT_ID

   AND B.TYPE = 'TM'

   AND C.OBJECT_NAME = 'TB_CLS_BOARD'

SELECT A.SID, A.SERIAL#

FROM V$SESSION A, V$LOCK B,

DBA_OBJECTS C

WHERE A.SID = B.SID

AND B.ID1 = C.OBJECT_ID

AND B.TYPE = 'TM'

AND C.OBJECT_NAME = 'TB_CLS_BOARD'

lock 제거

SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,

       A.PROGRAM, A.LOGON_TIME, 'ALTER SYSTEM KILL SESSION'''||A.SID||', '||A.SERIAL#||''';'

FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D

WHERE  X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID

ORDER BY LOGON_TIME;

SELECT DISTINCT X.SESSION_ID, A.SERIAL#, D.OBJECT_NAME, A.MACHINE, A.TERMINAL,

A.PROGRAM, A.LOGON_TIME, 'ALTER SYSTEM KILL SESSION'''||A.SID||', '||A.SERIAL#||''';'

FROM GV$LOCKED_OBJECT X, GV$SESSION A, DBA_OBJECTS D

WHERE X.SESSION_ID = A.SID AND X.OBJECT_ID = D.OBJECT_ID

ORDER BY LOGON_TIME;

ALTER SYSTEM KILL SESSION   'A.SID', 'A.SERIAL#';

1	ALTER SYSTEM KILL SESSION 'A.SID', 'A.SERIAL#';

Lock 발생 사용자 및 object 조회 + 어떤 sql 를 실행하여 lock 을 걸고 있는지 확인

SELECT DISTINCT T1.SESSION_ID
     , T2.SERIAL#
     , T4.OBJECT_NAME
     , T2.MACHINE
     , T2.TERMINAL
     , T2.PROGRAM
     , T3.ADDRESS
     , T3.PIECE
     , T3.SQL_TEXT
  FROM V$LOCKED_OBJECT T1
     , V$SESSION T2
     , V$SQLTEXT T3
     , DBA_OBJECTS T4
 WHERE 1=1
   AND T1.SESSION_ID = T2.SID
   AND T1.OBJECT_ID = T4.OBJECT_ID
   AND T2.SQL_ADDRESS = T3.ADDRESS
   ORDER BY T3.ADDRESS, T3.PIECE

SELECT DISTINCT T1.SESSION_ID

, T2.SERIAL#

, T4.OBJECT_NAME

, T2.MACHINE

, T2.TERMINAL

, T2.PROGRAM

, T3.ADDRESS

, T3.PIECE

, T3.SQL_TEXT

FROM V$LOCKED_OBJECT T1

, V$SESSION T2

, V$SQLTEXT T3

, DBA_OBJECTS T4

WHERE 1=1

AND T1.SESSION_ID = T2.SID

AND T1.OBJECT_ID = T4.OBJECT_ID

AND T2.SQL_ADDRESS = T3.ADDRESS

ORDER BY T3.ADDRESS, T3.PIECE

Lock발생 사용자와 sql, object 조회

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$locked_object x, v$session a, v$sqltext b, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
and a.sql_address = b.address
--and a.terminal = '홍길동'
order by b.address,b.piece;

select distinct x.session_id, a.serial#,d.object_name,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text

from v$locked_object x, v$session a, v$sqltext b, dba_objects d

where x.session_id = a.sid

and x.object_id = d.object_id

and a.sql_address = b.address

--and a.terminal = '홍길동'

order by b.address,b.piece;

Lock 발생 사용자확인

select distinct x.session_id,  a.serial#,d.object_name,a.machine,a.terminal,a.program, a.logon_time
--select  'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'
from gv$locked_object x, gv$session a, dba_objects d
where x.session_id = a.sid
and x.object_id = d.object_id
order by logon_time;

select distinct x.session_id, a.serial#,d.object_name,a.machine,a.terminal,a.program, a.logon_time

--select 'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'

from gv$locked_object x, gv$session a, dba_objects d

where x.session_id = a.sid

and x.object_id = d.object_id

order by logon_time;

현재 접속자의 sql 분석

select distinct a.sid,a.serial#,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text
from v$session a, v$sqltext b
where a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece;

select distinct a.sid,a.serial#,a.machine,a.terminal,a.program,b.address,b.piece, b.sql_text

from v$session a, v$sqltext b

where a.sql_address = b.address

order by a.sid, a.serial#,b.address,b.piece;

Apache Http 와 Tomcat 동접 늘리기 MariaDB MySQL datatype

Comments are currently closed.