Re: Checking SQL_TRACE status of a particular session

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Thu, 24 Jan 2008 06:48:16 -0800 (PST)
Message-ID: <e7cacc31-776d-42b9-b2bf-04933dfb2395@q21g2000hsa.googlegroups.com>

On Jan 24, 3:23 am, Peter Teoh <htmldevelo..._at_gmail.com> wrote:
> Thanks, after some digging, read_ev() can be used for the current
> session. But if I would like to check on ALL THE SESSION, then there
> seemed no way:
>
> http://www.dbforums.com/archive/index.php/t-1086400.html
>
> Thanks.

I am sure there is a more elegant way with oradebug, or something similar. However, you could always read each spid in v$process and check for a corresponding trace file in udump.

set serveroutput on size 1000000
declare
  l_udump varchar2(300);
begin
  select value
    into l_udump
    from v$parameter
    where name = 'user_dump_dest';
  for cur in (select spid

                from v$process p,
                     v$session s
                where s.paddr = p.addr) loop

    dbms_output.put_line('ls -lrt ' || l_udump || '/*' || cur.spid || '*');
  end loop;
end;
/ Received on Thu Jan 24 2008 - 08:48:16 CST