V$DIAG_TRACE_FILE_CONTENT 뷰

12.2 버전에 V$DIAG_TRACE_FILE_CONTENTS 뷰가 추가되어 트레이스 파일의 내용을 쿼리로 조회할 수 있게 되었다.

먼저 아래 쿼리로 트레이스 파일을 생성한 세션의 adr_home과 trace_filename을 조회하자.

-- 1
SELECT REGEXP_SUBSTR (tracefile, '(.+)[/\]trace[/\](.+\.trc)', 1, 1, 'i', '1') AS adr_home
     , REGEXP_SUBSTR (tracefile, '(.+)[/\]trace[/\](.+\.trc)', 1, 1, 'i', '2') AS trace_filename
  FROM v$process
 WHERE addr = (SELECT paddr
                 FROM v$session
                WHERE sid = NVL (:v_sid, SYS_CONTEXT ('USERENV', 'SID')));

ADR_HOME                                     TRACE_FILENAME
-------------------------------------------- --------------------------
C:\APP\ORA12CR2\diag\rdbms\ora12cr2\ora12cr2 ora12cr2_ora_6804_TUNA.trc

1개의 행이 선택되었습니다.

V$DIAG_TRACE_FILE_CONTENTS 뷰를 조회한 결과다.

-- 2
SELECT   REPLACE (REPLACE (payload, CHR (13)), CHR (10)) AS payload
    FROM v$diag_trace_file_contents
   WHERE adr_home = 'C:\APP\ORA12CR2\diag\rdbms\ora12cr2\ora12cr2'
     AND trace_filename = 'ora12cr2_ora_6804_TUNA.trc'
ORDER BY line_number;

PAYLOAD
-----------------------------------------------------------------------------------------------------------------------------------------
Trace file C:\APP\ORA12CR2\diag\rdbms\ora12cr2\ora12cr2\trace\ora12cr2_ora_6804_TUNA.trc
...
WAIT #317163998064: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=791712898068
WAIT #317163998064: nam='SQL*Net message from client' ela= 1342 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=791712931264
CLOSE #317163998064:c=0,e=12,dep=0,type=1,tim=791712931397
=====================
PARSING IN CURSOR #317163991984 len=18 dep=0 uid=136 oct=3 lid=136 tim=791712933054 hv=3991932091 ad='7ffbd1a12760' sqlid='9g6pyx7qz035v'
SELECT * FROM DUAL
END OF STMT
PARSE #317163991984:c=0,e=1578,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=272002086,tim=791712933052
EXEC #317163991984:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=791712933266
WAIT #317163991984: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=791712933357
FETCH #317163991984:c=0,e=55,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=272002086,tim=791712933469
STAT #317163991984 id=1 cnt=1 pid=0 pos=1 obj=142 op='TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 str=1 time=46 us cost=2 size=2 card=1)'
WAIT #317163991984: nam='SQL*Net message from client' ela= 888 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=791712934538
FETCH #317163991984:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=272002086,tim=791712934635
WAIT #317163991984: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=791712934685
WAIT #317163991984: nam='SQL*Net message from client' ela= 978 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=791712935699
CLOSE #317163991984:c=0,e=11,dep=0,type=0,tim=791712935810
=====================
PARSING IN CURSOR #317163991984 len=55 dep=0 uid=136 oct=42 lid=136 tim=791712935919 hv=524428051 ad='0' sqlid='6tk93gwgn48sm'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
END OF STMT
PARSE #317163991984:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=791712935918
EXEC #317163991984:c=0,e=888,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=791712936929

45 행이 선택되었습니다.