ITPub博客

首页 > 数据库 > Oracle > [20191221]12c查询跟踪文件内容.txt

[20191221]12c查询跟踪文件内容.txt

原创 Oracle 作者:lfree 时间:2019-02-21 15:57:31 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2636592/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2852
  • 访问量
    6641158