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