以下脚本可以用于列出本会话(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;
/
你好,我认为脚本是否可以写成下面?脚本中的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;
/
你好，
SET_MODULE一般我是在做测试的时候用，若是产品环境的话确实不宜这样设置， 感谢你的指出。
trace location 的话， 2种方法都是可以的。
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;
/
—==