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

Comments

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

1. 

   wan qisheng

   December 30, 2011

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

   Reply
   1. 

      Maclean Liu

      December 30, 2011

      你好，

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

      Reply
2. 

   chengwill

   February 6, 2012

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

   Reply