10046的trace file可以使用工具trca进行分析

安装如下：

conn / as sysdba

@/home/oracle/install/tacreate.sql

指定一个密码，不指定会报错退出

指定默认表空间，注意不能指定SYSTEM表空间，否则会报错

指定临时表空间，就使用系统默认的就可以

Sat Dec 26 page 1
Index unique scans and counts

LINE/COL ERROR
-------- -----------------------------------------------------------------
49/3 PL/SQL: Item ignored
51/14 PLS-00201: identifier 'V$EVENT_NAME' must be declared
73/16 PL/SQL: SQL Statement ignored
73/30 PL/SQL: ORA-00942: table or view does not exist
75/9 PL/SQL: Statement ignored
75/19 PLS-00364: loop index variable 'I' use is invalid
76/9 PL/SQL: Statement ignored
76/19 PLS-00364: loop index variable 'I' use is invalid
77/9 PL/SQL: Statement ignored
77/19 PLS-00364: loop index variable 'I' use is invalid
131/5 PL/SQL: SQL Statement ignored
137/12 PL/SQL: ORA-00942: table or view does not exist
244/18 PL/SQL: Item ignored
244/18 PLS-00201: identifier 'V$INSTANCE' must be declared
944/13 PL/SQL: Statement ignored
944/80 PLS-00487: Invalid reference to variable 'WAITPARS_TYPE'
950/13 PL/SQL: Statement ignored
950/69 PLS-00487: Invalid reference to variable 'WAITPARS_TYPE'
956/13 PL/SQL: Statement ignored
956/64 PLS-00487: Invalid reference to variable 'WAITPARS_TYPE'
Creating Package Specs TRCA$I...
No errors.
Creating Package Body TRCA$I...
No errors.
Creating Grant and Synonym on TRCA$I...

Taking a snapshot of DBA_EXTENTS (may take several minutes)
BEGIN trca$i.refresh_trca$_extents; END;

*
ERROR at line 1:
ORA-04063: package body "TRCANLZR.TRCA$D" has errors
ORA-06508: PL/SQL: could not find program unit being called: "TRCANLZR.TRCA$D"
ORA-06512: at "TRCANLZR.TRCA$I", line 403
ORA-06512: at line 1

查看源码是tacpkgd.pkb执行失败了

手工执行还是报错，报'V$EVENT_NAME' 没有定义的错误

使用trcanlzr用户访问'V$EVENT_NAME' 没有问题，但是就是编译不过去呢，估计是权限的问题，加大权限：

grant select any dictionary to trcanlzr；

tacpkgd.pkb编译成功，说明就是权限的问题

然后把剩余的部分执行

ok，其他的一切顺利。

分析trace file：

SQL> @/home/oracle/run/trcanlzr.sql bss_ora_6520.trc;
SQL> REM
SQL> REM $Header: trcanlzr.sql 2.4.6 2007/08/28 csierra $
SQL> REM
SQL> REM trcanlzr.sql
SQL> REM
SQL> REM Copyright (c) 2005, Oracle Corporation. All rights reserved.
SQL> REM
SQL> REM NAME
SQL> REM trcanlzr.sql
SQL> REM
SQL> REM DESCRIPTION
SQL> REM Provide trace filename to be analyzed (case sensitive).
SQL> REM
SQL> REM NOTES
SQL> REM Execute on same instance and under same schema user that generated
SQL> REM the raw SQL Trace being analyzed. If executed on a different instance
SQL> REM or user, details as explain plans and I/O summaries are skipped.
SQL> REM
SQL> REM PARAMETERS
SQL> REM 1. Name of trace to be analyzed (required) Must be in UDUMP
SQL> REM
SQL> REM EXAMPLE
SQL> REM SQL> start trcanlzr.sql ;
SQL> REM SQL> start trcanlzr.sql vis_ora_14328_SYSADMIN.trc;
SQL> REM
SQL> REM MODIFIED (MM/DD/YY)
SQL> REM csierra 12/29/05 - Created
SQL> REM csierra 05/29/07 - 2.4.3
SQL> REM csierra 08/23/07 - 2.4.5
SQL> REM csierra 08/28/07 - 2.4.6
SQL> REM
SQL> REM
SQL> SET ECHO OFF;

Value passed: bss_ora_6520.trc
~~~~~~~~~~~~

...generating trcanlzr report into UDUMP server directory

Trace Analyzer Report "trcanlzr_6520_1.html" has been created in /u01/app/oracle/admin/bss/udump

...trcanlzr report has been generated into UDUMP server directory

...copying trcanlzr report into local SQL*Plus client directory
...trcanlzr report was copied from UDUMP into local SQL*Plus directory

ok，生成了HTML文件

the end；

[@more@]