ORACLE TRACE USER USING TRIGGER
from OTN : create trigger http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#SQLRF01405
1. Trace 와 Trigger
A.
TRACE 방법은 AUTOTRACE 와 SQL_TRACE, ORADEBUG, SET EVENTS 를 사용한
방법이
있다.
B.
TRIGGER
는 PL SQL 블록의 실행시점을
암시적(자동)으로 정의할 수 있다.
C.
이번 글에서는 TRACE 와 TRIGGER 를 같이 사용하여 DB에 접근한 세션에 대하여
자동으로 트레이스를 수행하고 이후 트레이스 정보와 대기이벤트 정보등을 조회하는
방법에 대해 이야기 하겠다.
2. 생성구문
SQL> create or replace
trigger logon_trace_on
2
after logon on database
3
begin
4
execute immediate 'alter session set events ''10046 trace name context
forever, level 8''';
5 end;
6 /
Trigger created.
SQL> create or replace
trigger logon_trace_off
2
before logoff on database
3
begin
4
execute immediate 'alter session set events ''10046 trace name context
off''';
5 end;
6 /
Trigger created.
접근 세션에 대해 트레이스 하기 위해 사용한 실행 시점은 after logon on database 와 before logoff on
database 이다.전자의 경우는 커넥션을 맺고난 후 실행하란 의미이며 후자는 logoff 하기전에 실행하란 의미이다. PL/SQL 블록 부분엔 DDL 을 사용하기에 DYNAMIC SQL 을 사용하였다. trace 개시 후 off 하지 않는 경우 시스템에 심각한 부하를
초래할 수 있다.
3. 결과의 활용
A.
결과는 user_dump_dest 에 <instance_name>_ora_<spid>.trc
로 생성된다. spid 는 v$process 의 spid 를 말한다.spid 는 os
의 process id 를 의미한다. 이를 사용하면
시스템 레벨에서 session 을 kill 할 수도 있다. 현재 세션의 spid 를 조회하는 쿼리는 아래와 같다.
SQL> select spid from v$process
2 where addr = (
3 select paddr from v$session
4 where sid in (select distinct
sid from v$mystat));
SPID
------------
5722
v$process 에는 sid 정보가 없기에 inline view 를
사용해 조회하였다.
위 SPID를 근거로 user_dump_dest 에 생성된 파일을 tkprof 명령어를 사용해 정리해 보면 된다.
사용법은 tkprof 원본파일 새로운파일 로 입력하면된다.
해당 프로세스를 os 레벨에서 kill 하려면 아래와 같은 과정을 밟으면 된다.
<<OS>>
RH3@PROD : /home/oracle>$ps -ef | grep 5722
oracle
5722 5721 0 19:17 ? 00:00:00 oraclePROD (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle
5757 4392 0 19:22 pts/3 00:00:00 grep 5722
RH3@PROD : /home/oracle>$kill -9 5722
<<ORACLE>>
SQL> select spid from v$process
2 where addr = (
3 select paddr from v$session
4 where sid in (select distinct sid from
v$mystat));
SPID
------------
5722
SQL>
~~~ OS 단에서 KILLED ~~~
SQL> select * from tab;
select * from tab
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL> desc tab
ERROR:
ORA-03114: not connected to ORACLE
SQL>