-- 查询数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
-- 查询视图 v$diag_info
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- -------------------- --------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base d:\app\administrator
1 ADR Home d:\app\administrator\diag\rdbms\orcl\orcl
1 Diag Trace d:\app\administrator\diag\rdbms\orcl\orcl\trace
1 Diag Alert d:\app\administrator\diag\rdbms\orcl\orcl\alert
1 Diag Incident d:\app\administrator\diag\rdbms\orcl\orcl\incident
1 Diag Cdump d:\app\administrator\diag\rdbms\orcl\orcl\cdump
1 Health Monitor d:\app\administrator\diag\rdbms\orcl\orcl\hm
1 Default Trace File d:\app\administrator\diag\rdbms\orcl\orcl\trace\or
cl_ora_5744.trc
INST_ID NAME VALUE
---------- -------------------- --------------------------------------------------
1 Active Problem Count 0
1 Active Incident Coun 0
t
-- 条件查询视图 v$diag_info where name = ‘ v$diag_info’
SELECT value FROM v$diag_info WHERE name='Default Trace File';
-- 改变系统 sql_trace = true
SQL> alter system set sql_trace=true;
系统已更改。
-- sql查询
SQL> select * from dual;
DU
--
X
-- 改变系统 sql_trace = false
SQL> alter system set sql_trace=false;
系统已更改。
-- 查看trc文件，得到相关sql查询信息
=====================
PARSING IN CURSOR #16 len=18 dep=0 uid=0 oct=3 lid=0 tim=6209383360 hv=942515969 ad='7ff73f205f8' sqlid='a5ks9fhw2v9s1'
select * from dual
END OF STMT
PARSE #16:c=15600,e=34241,p=0,cr=3,cu=0,mis=1,r=0,dep=0,og=1,plh=272002086,tim=6209383358
EXEC #16:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=272002086,tim=6209383572
FETCH #16:c=0,e=7916,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=272002086,tim=6209391595
STAT #16 id=1 cnt=1 pid=0 pos=1 obj=116 op='TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=0 us cost=2 size=2 card=1)'
FETCH #16:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=272002086,tim=6209392413
*** 2012-10-15 22:17:48.640
CLOSE #16:c=0,e=49,dep=0,type=0,tim=6218121671
=====================