[20191221]12c查詢跟蹤檔案內容.txt

[20191221]12c查詢跟蹤檔案內容.txt

--//12cR2下查詢跟蹤檔案內容,可以透過檢視V$DIAG_TRACE_FILE定位檔案.使用V$DIAG_TRACE_FILE_CONTENTS檢視跟蹤檔案內容.

--//透過簡單的例子說明問題:

1.環境:

SYS@xxxxxx:1521/orcl> select BANNER from v$version;

BANNER

----------------------------------------------------------------------

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

--//18c一樣可以演示.

SYS@xxxxxx:1521/orcl> @ spid

       SID    SERIAL# PROCESS SERVER             SPID       PID  P_SERIAL# C50

---------- ---------- ------- ------------------ ------ ------- ---------- --------------------------------------------------

        17      58402 16889   DEDICATED          21820       88       8797 alter system kill session '17,58402' immediate;

SYS@xxxxxx:1521/orcl> @ P

   INST_ID NAME                 VALUE                                                                                      CON_ID

---------- -------------------- -------------------------------------------------------------------------------------- ----------

         1 Default Trace File   /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_21820.trc                          0

2.測試:

SYS@xxxxxx:1521/orcl> @ 10046on 12

Session altered.

SYS@xxxxxx:1521/orcl> select SysDate from dual ;

SYSDATE

-------------------

2019-02-21 15:50:45

SYS@xxxxxx:1521/orcl> @ 10046off

Session altered.

SYS@xxxxxx:1521/orcl> select adr_home,trace_filename,change_time from v$diag_trace_file order by change_time desc fetch first 5 rows only;

ADR_HOME                                   TRACE_FILENAME          CHANGE_TIME

------------------------------------------ ----------------------- ------------------------------

/u01/app/oracle/diag/rdbms/orclcdb/orclcdb orclcdb_ora_21820.trc   2019-02-21 15:51:24.000 +08:00

/u01/app/oracle/diag/rdbms/orclcdb/orclcdb orclcdb_cjq0_8994.trc   2019-02-21 10:00:00.000 +08:00

/u01/app/oracle/diag/rdbms/orclcdb/orclcdb orclcdb_j000_27096.trc  2019-02-21 06:00:01.000 +08:00

/u01/app/oracle/diag/rdbms/orclcdb/orclcdb orclcdb_j000_12208.trc  2019-02-20 06:00:02.000 +08:00

/u01/app/oracle/diag/rdbms/orclcdb/orclcdb orclcdb_j000_29061.trc  2019-02-19 06:00:02.000 +08:00

--//可以看出倒排序第1個就是當前生成的跟蹤檔案.以前必須要到後臺檢視跟蹤檔案內容.現在不需要,僅僅查詢檢視:

SYS@xxxxxx:1521/orcl> select payload c140 from V$DIAG_TRACE_FILE_CONTENTS where ADR_HOME='/u01/app/oracle/diag/rdbms/orclcdb/orclcdb' and trace_filename='orclcdb_ora_21820.trc';

C140

---------------------------------------------------------------------------------

Trace file /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_ora_21820.trc

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

Build label:    RDBMS_18.1.0.0.0_LINUX.X64_180103.1

ORACLE_HOME:    /u01/app/oracle/product/18.0.0/db_home

System name:    Linux

Node name:      hosp2

Release:        4.1.12-112.16.4.el7uek.x86_64

Version:        #2 SMP Mon Mar 12 23:57:12 PDT 2018

Machine:        x86_64

Instance name: orclcdb

Redo thread mounted by this instance: 1

Oracle process number: 88

Unix process pid: 21820, image: oracle@hosp2

*** 2019-02-21T15:50:32.810834+08:00 (ORCL(3))

*** SESSION ID:(17.58402) 2019-02-21T15:50:32.810867+08:00

*** CLIENT ID:() 2019-02-21T15:50:32.810873+08:00

*** SERVICE NAME:(orcl) 2019-02-21T15:50:32.810878+08:00

*** MODULE NAME:(sqlplus@gxqyydg4 (TNS V1-V3)) 2019-02-21T15:50:32.810883+08:00

*** ACTION NAME:() 2019-02-21T15:50:32.810888+08:00

*** CLIENT DRIVER:(SQL*PLUS ) 2019-02-21T15:50:32.810893+08:00

*** CONTAINER ID:(3) 2019-02-21T15:50:32.810898+08:00

WAIT #140040343033784: nam='Disk file operations I/O' ela= 58 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=8577113998376

WAIT #140040343033784: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8577113999258

*** 2019-02-21T15:50:45.145102+08:00 (ORCL(3))

WAIT #140040343033784: nam='SQL*Net message from client' ela= 12334057 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8577126333336

CLOSE #140040343033784:c=8,e=8,dep=0,type=1,tim=8577126333446

=====================

PARSING IN CURSOR #140040317036936 len=25 dep=0 uid=0 oct=3 lid=0 tim=8577126334502 hv=297459922 ad='28a91d140' sqlid='6p3a1sn8vps6k'

select SysDate from dual

~~~~~~~~~~->我執行的命令

END OF STMT

PARSE #140040317036936:c=752,e=1021,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=8577126334501

EXEC #140040317036936:c=30,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=8577126334592

WAIT #140040317036936: nam='Disk file operations I/O' ela= 23 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=8577126334640

WAIT #140040317036936: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8577126334678

FETCH #140040317036936:c=10,e=10,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=8577126334711

STAT #140040317036936 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=1 us cost=2 size=0 card=1)'

WAIT #140040317036936: nam='SQL*Net message from client' ela= 701 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8577126335497

FETCH #140040317036936:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1388734953,tim=8577126335528

WAIT #140040317036936: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8577126335544

*** 2019-02-21T15:51:24.601851+08:00 (ORCL(3))

WAIT #140040317036936: nam='SQL*Net message from client' ela= 39454527 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=8577165790081

CLOSE #140040317036936:c=10,e=10,dep=0,type=0,tim=8577165790222

=====================

PARSING IN CURSOR #140040317036936 len=55 dep=0 uid=0 oct=42 lid=0 tim=8577165790435 hv=2217940283 ad='0' sqlid='06nvwn223659v'

alter session set events '10046 trace name context off'

END OF STMT

PARSE #140040317036936:c=150,e=149,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=8577165790434

EXEC #140040317036936:c=332,e=333,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=8577165790817

51 rows selected.