[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.