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

以下脚本可以用于列出本会话(local session)的sid、pid、spid、trace 路径等有用信息; 因为不依赖于dbms_session等包体,所以仅需要select_catalog_role的角色即可执行,更为灵活。

 

 

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;
/

Posted

in

by

Tags:

Comments

3 responses to “Script:列出本会话的细节信息”

  1. wan qisheng Avatar
    wan qisheng

    你好,我认为脚本是否可以写成下面?脚本中的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;
    /

    1. Maclean Liu Avatar

      你好,

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

  2. chengwill Avatar
    chengwill

    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;
    /
    —==

Leave a Reply

Your email address will not be published. Required fields are marked *