问题描述
学习盖老师的博客上的10046事件，链接http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm
出现如下错误，请帮忙看看。
SQL> select sid,serial#,username from v$session where username is not null;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
33 833 SYS
36 789 SYS
37 1583 SYS
38 425 SCOTT
SQL> exec dbms_system.set_ev(38,425,10046,8,'scott');
PL/SQL procedure successfully completed.
SQL> conn / ^H^H
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL> conn scott/tiger;
Connected.
SQL> select count(1) from dba_users;
COUNT(1)
----------
10
SQL> conn / as sysdba;
Connected.
SQL> exec dbms_system.set_ev(38,425,10046,0,'scott');
PL/SQL procedure successfully completed.
SQL> select d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from
2 ( select p.spid
3 from sys.v$mystat m,sys.v$session s,sys.v$process p
4 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
5 ( select t.instance from sys.v$thread t,sys.v$parameter v
6 where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
7 ( select value from sys.v$parameter where name = 'user_dump_dest') d;
TRACE_FILE_NAME
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_70851.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mysql ~]$ more /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_70851.trc
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_70851.trc: No such file or directory
[oracle@mysql ~]$
专家解答
测试存在很大问题，首先你开启了38号会话的trace，但是如果我没有猜错，你所有的测试都是用一个终端执行的，所以当你用SYSDBA登陆设置会话时，之前你的38号会话已经结束了。后面你再登陆SCOTT，对应的会话是什么，你并没有查询，不过就算查询了也没有意义，因为你设置开启的不是你后面登陆的会话。
你最后查出来的trace名称对应的是当前SYS用户的trace，但是你并没有开启当前会话的trace，所以没有跟踪文件时正常的。
感觉你并没有理解TRACE的含义，再去把那段文章仔细读一遍吧。
