做个10046事件真麻烦

--在PL/SQL DELOVER COMMAND 下 获得当前会话ID
SELECT SYS_CONTEXT ('USERENV', 'SID') sid FROM DUAL;

--启动10046跟踪
alter session set events '10046 trace name context forever,level 12';

-- 执行过程

declare
type type_owner is table of zengfankun_temp01.owner%type index by binary_integer;
type type_object_name is table of zengfankun_temp01.object_name%type index by binary_integer;
type type_object_id is table of zengfankun_temp01.object_id%type index by binary_integer;
type type_object_type is table of zengfankun_temp01.object_type%type index by binary_integer;
type type_last_ddl_time is table of zengfankun_temp01.last_ddl_time%type index by binary_integer;

l_ary_owner type_owner;
l_ary_object_name type_object_name;
l_ary_object_id type_object_id;
l_ary_object_type type_object_type;
l_ary_last_ddl_time type_last_ddl_time;

cursor cur_object is
   select owner,object_name,object_id,object_type,last_ddl_time
   from zengfankun_temp01
   order by owner,object_name,object_type,last_ddl_time;
begin
  open cur_object;
  loop
    fetch  cur_object bulk collect into
      l_ary_owner,
      l_ary_object_name,
      l_ary_object_id,
      l_ary_object_type,
      l_ary_last_ddl_time
   limit 10000;
   exit when cur_object%notfound or cur_object%notfound is null;  
 end loop;  
end;

--关闭跟踪

alter session set events '10046 trace name context off';

 

--在另外个SQL窗口

Select Rtrim(c.Value, '/') || Decode(e.Plat_Id, 1, '\', '/') ||
       d.Instance_Name || '_ora_' || Ltrim(To_Char(a.Spid)) || '.trc' Trace_File
  From V$process a,
       V$session b,
       V$parameter c,
       V$instance d,
       (Select Case
                 When t.Banner Like '%Windows%' Then
                  1
                 When t.Banner Like '%Linux%' Then
                  2
                 When t.Banner Like '%AIX%' Then
                  3
                 When t.Banner Like '%Solaris%' Then
                  4
               End Plat_Id
          From V$version t
         Where t.BANNER Like '%TNS%') e
 Where a.Addr = b.Paddr
   And b.Sid = &P_SID
   And c.Name = 'user_dump_dest';  
--转换TRC

tkprof D:\ORACLE\PRODUCT\10.2.0\ADMIN\SHARK_OLAP\UDUMP\shark_ora_16942.trc D:\output10.txt