在Oracle中跟踪某几个用户的SQL

在Oracle中跟踪某几个用户的SQL

在Oracle中做SQL跟踪，估计大部分都会用sqlplus中的autotrace或者设置10046跟踪。但是，如果是调试某个应用系统的话，特别是打开了连接池的系统，靠以上两种方法基本上就歇菜了。还是SQL Server的SQL Server Profiler好啊。

最近在看冯大辉翻译的《Oracle性能诊断艺术》，发现一个很好的技巧，思路是建一个Logon，现摘录如下（稍作了一个修改）：

01	create role trace_10046_role;

02

03	CREATE OR REPLACE TRIGGER SYS.set_10046_trace_on_logon

04	AFTER LOGON ON DATABASE

05

BEGIN

06	if ( dbms_session.is_role_enabled('trace_10046_role') and user not in ('SYS','SYSTEM')  ) then

07	EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';

08	EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';

09	EXECUTE IMMEDIATE 'alter session set tracefile_identifier='''||user||'_10046''';

10	EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';

11

end if;

12	END set_10046_trace_on_logon;

启用和禁用对系统进行做10046跟踪SQL：

 

01	SQL> show user;

02	USER is "SYS"

03	SQL> grant trace_10046_role to cms_text;

04	SQL> select * from dba_ROLE_PRIVS where granted_role='TRACE_10046_ROLE';

05

06	GRANTEE                        GRANTED_ROLE                   ADM DEF

07	------------------------------ ------------------------------ --- ---

08	SYS                            TRACE_10046_ROLE               YES YES

09	CMS_TEXT                       TRACE_10046_ROLE               NO  YES

10	Grant succeeded.

11	SQL> revoke trace_10046_role from cms_text;

12	SQL> select * from dba_ROLE_PRIVS where granted_role='TRACE_10046_ROLE';

13

14	GRANTEE                        GRANTED_ROLE                   ADM DEF

15	------------------------------ ------------------------------ --- ---

16	SYS                            TRACE_10046_ROLE               YES YES

其实就是将 trace_10046_role grant/revoke 给相应的用户。

当具有 trace_10046_role 角色的用户登录后在udump下就会出现相应的trc文件：

 

1	[oracle@test-server udump]$ pwd

2	/u01/app/admin/ora8i/udump

3	[oracle@test-server udump]$ ls

4	ora8i_ora_8259_CMS_TEXT_10046.trc