Script:列出本会话的细节信息

set serveroutput on
set linesize 200 pagesize 1400
set time on
declare
  l_audsid number;
  l_sid    number;
  l_serial number;
  l_module varchar2(128);
  l_pid    number;
  l_spid   number;
  l_trace  varchar2(2000);
  l_user   varchar2(128);
begin
  DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'Maclean Liu OppO',
                                   action_name => 'ACTIVE');
  select audsid, sid, SERIAL#, module,username
    into l_audsid, l_sid, l_serial, l_module,l_user
    from v$session
   where sid = (select distinct sid from v$mystat);
  select pid, spid
    into l_pid, l_spid
    from v$process
   where addr = (select paddr
                   from v$session
                  where sid = l_sid
                    and serial# = l_serial);
  SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
         p.spid || '.trc'
    into l_trace
    FROM (SELECT p.spid
            FROM v$mystat m, v$session s, v$process p
           WHERE m.statistic# = 1
             AND s.SID = m.SID
             AND p.addr = s.paddr) p,
         (SELECT t.INSTANCE
            FROM v$thread t, v$parameter v
           WHERE v.NAME = 'thread'
             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;

  dbms_output.enable(9999999);
  dbms_output.put_line('===============================================');
  dbms_output.put_line(' USERNAME=' || l_user);
  dbms_output.put_line(' SESSION ID=' || l_sid || '  SERIAL#=' || l_serial);
  dbms_output.put_line(' AUDSID=' || l_audsid || '      MODULE#=' ||
                       l_module);
  dbms_output.put_line(' PID=' || l_pid || '          SPID#=' || l_spid);
  dbms_output.put_line(' TRACE_FILE_LOCATION=' || l_trace);
  dbms_output.put_line('===============================================');
  commit;
end;
/

Oracle, Oracle脚本script   Oracle脚本script, session, trace  

3 comments to Script:列出本会话的细节信息

• wan qisheng

  December 30, 2011 at 3:48 PM · Reply

  你好,我认为脚本是否可以写成下面?脚本中的SET_MODULE似不当,脚本可能被其他脚本调用来显示这些信息,所以不赞同修改module info. l_trace获取似乎过于复杂.

  set serveroutput on
  set linesize 200 pagesize 1400
  declare
  l_audsid number;
  l_sid number;
  l_serial number;
  l_module varchar2(128);
  l_pid number;
  l_spid number;
  l_trace varchar2(2000);
  l_user varchar2(128);
  l_paddr raw(8);
  begin
  select audsid,sid,serial#, module,username,paddr
  into l_audsid, l_sid,l_serial, l_module,l_user,l_paddr
  from v$session
  where sid = (select distinct sid from v$mystat);
  select pid, spid
  into l_pid, l_spid
  from v$process
  where addr = l_paddr;

  select d.VALUE || ‘/’ || lower(i.INSTANCE_name) || ‘_ora_’ ||
  l_spid || ‘.trc’
  into l_trace
  from v$parameter d,v$instance i
  where d.NAME = ‘user_dump_dest’;

  dbms_output.enable(9999999);
  dbms_output.put_line(‘===============================================’);
  dbms_output.put_line(‘ USERNAME=’ || l_user);
  dbms_output.put_line(‘ SESSION ID=’ || l_sid || ‘ SERIAL#=’ || l_serial);
  dbms_output.put_line(‘ AUDSID=’ || l_audsid || ‘ MODULE#=’ ||l_module);
  dbms_output.put_line(‘ PID=’ || l_pid || ‘ SPID#=’ || l_spid);
  dbms_output.put_line(‘ TRACE_FILE_LOCATION=’ || l_trace);
  dbms_output.put_line(‘===============================================’);
  commit;
  end;
  /

  • Maclean Liu

    December 30, 2011 at 7:41 PM · Reply

    你好，

    SET_MODULE一般我是在做测试的时候用，若是产品环境的话确实不宜这样设置， 感谢你的指出。
    trace location 的话， 2种方法都是可以的。

• chengwill

  February 6, 2012 at 4:05 PM · Reply

  Hello all

  That’s perfect things! Thanks for sharing it!

  There are some output formats has adjusted, please look through it.

  –==
  set serveroutput on
  set linesize 200 pagesize 1400
  declare
  l_audsid number;
  l_sid number;
  l_serial number;
  l_module varchar2(128);
  l_pid number;
  l_spid number;
  l_trace varchar2(2000);
  l_user varchar2(128);
  l_paddr raw(8);
  begin
  select audsid, sid, serial#, module, username, paddr
  into l_audsid, l_sid, l_serial, l_module, l_user, l_paddr
  from v$session
  where sid = (select distinct sid from v$mystat);
  select pid, spid into l_pid, l_spid from v$process where addr = l_paddr;

  select d.VALUE || ‘/’ || lower(i.INSTANCE_name) || ‘_ora_’ || l_spid || ‘.trc’
  into l_trace
  from v$parameter d, v$instance i
  where d.NAME = ‘user_dump_dest’;

  dbms_output.enable(9999999);
  dbms_output.put_line(‘===============================================’);
  dbms_output.put_line(‘ USERNAME=’ || l_user);
  dbms_output.put_line(‘ SESSION ID=’ || l_sid || ‘ SERIAL#=’ || l_serial);
  dbms_output.put_line(‘ AUDSID=’ || l_audsid || ‘ MODULE#=’ ||l_module);
  dbms_output.put_line(‘ PID=’ || l_pid || ‘ SPID#=’ || l_spid);
  dbms_output.put_line(‘ TRACE_FILE_LOCATION=’ || l_trace);
  dbms_output.put_line(‘===============================================’);
  commit;
  end;
  /
  —==

Copyright © 2012 Oracle Clinic – Maclean Liu的个人技术博客 - All Rights Reserved
Powered by WordPress & Atahualpa