행위

DBA 모니터링 sqlplus용

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 12월 28일 (목) 11:07 판
thumb_up 추천메뉴 바로가기

1 DBA Sqlplus용 모니터링 SQL[편집]

  1. 서버 터미널에서 sqlplus를 이용하여 모니터링
  2. sqplus 에서 @xx 로 수행 (예시 xx.sql 로 저장한경우)

1.1 현재 사용중인 SQL 조회[편집]

-- s.sql
set linesize 1000
set pagesize 1200
set recsep off
set verify off
set feedback on
clear column

col username1 heading "UserName" format a7
col sid1 heading "SID/Ser#" format a10
col status1 heading "S" format a10
col blocking1 heading "Blocking" format a11
col wait_event1 heading "Wait_Event" format a20
col pga1 heading "PGA" format 9999
col lce1 heading "LCET" format 99999
col module heading "MODULE" format a10
col action heading "ACTION" format a20
col sqlid heading "SQL_ID" format a15
col sql_text1 heading "SQL" format a51
col seconds_in_wait heading "W/sec" format 9999 
col osuser1 heading "OSUSER" format a10 
col user_info1 heading "USER_INFO" format a15 
col spid1 heading "SPID" format a9 
col cpid1 heading "CPID" format a9 
col logon1 heading "LOGON" format a18
col machine1 heading "MACHINE" format a12
col kill1 heading "Kill_OS" format a20
col kill2 heading "Kill_DB" format a50

select substr(s.username,1,8) as username1
     , to_char(s.sid)||','||to_char(s.serial#) as sid1
     , substr(status,1,1) as status1
     , s.module
--     , s.action as action
     , s.seconds_in_wait as seconds_in_wait
     , substr(s.event,1,25) as wait_event1
     , last_call_et as lce1
     , s.sql_id as sqlid
     , trim((select substr(sql_text,1,71) form v$sql sq where sq.sql_id=s.sql_id and rownum<=1)) as sql_text1
     , s.osuser as osuser1
     , s.machine as machine1
     , to_char(logon_time,'yyyymmdd HH24:MI:SS') as logon1
     , s.process as cpid1
     , p.spid as spid1
     , s.sql_hash_value
     , 'kill -9 ' as kill1
     , 'alter system kill session '||''''||s.sid||','||s.erial#||''''||';' as kill2
--   , s.terminal as user_info1
  from v$session s
     , v$process p
 where s.paddr = p.addr
   and s.status = 'ACTIVE'
   and s.useranem is not null
   and s.event not in ('SQL*Net message from client','rdbms ipc message','ges remote message')
   and (machine,port) <> (select machine,port from v$session where sid=sys_context('USERENV','SID')) -- myself session 
 order by status1 desc,username1 desc,substr(s.action,1,3) desc ,module,lce1;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=DBA_모니터링_sqlplus용&oldid=10552"