Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1447302
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19
文章分类

全部博文(239)

文章存档

2014年(4)

2013年(22)

2012年(140)

2011年(14)

2010年(59)

我的朋友

分类: Oracle

2012-10-29 16:30:44

来自TOM的showsql.sql
It shows everyone logged in and if they are active, what they are doing and how long they've been doing it. If someone is executing PLSQL, what you will see will depend on what the plsql is currently doing. If the plsql is doing SQL, you'll see the SQL. if the plsql is doing lots of PLSQL work -- you'll see that code. What I like to do is have everyone "instrument" their code with calls to dbms_application_info which can fill in the client_info, action, and module columns in v$session. In this fashion, you can see where in a procedure someone is based on the values in these columns. showsql exposes this information to you as well. sqlplus uses it to show you what script someone is running for example... ---------------- showsql.sql --------------------------

点击(此处)折叠或打开

  1. column status format a10
  2. set feedback off
  3. set serveroutput on

  4. select username, sid, serial#, process, status
  5. from v$session
  6. where username is not null
  7. /

  8. column username format a20
  9. column sql_text format a55 word_wrapped

  10. set serveroutput on size 1000000
  11. declare
  12.     x number;
  13. begin
  14.     for x in
  15.     ( select username||'('||sid||','||serial#||
  16.                 ') ospid = ' || process ||
  17.                 ' program = ' || program username,
  18.              to_char(LOGON_TIME,' Day HH24:MI') logon_time,
  19.              to_char(sysdate,' Day HH24:MI') current_time,
  20.              sql_address, LAST_CALL_ET
  21.         from v$session
  22.        where status = 'ACTIVE'
  23.          and rawtohex(sql_address) <> '00'
  24.          and username is not null order by last_call_et )
  25.     loop
  26.         for y in ( select max(decode(piece,0,sql_text,null)) ||
  27.                           max(decode(piece,1,sql_text,null)) ||
  28.                           max(decode(piece,2,sql_text,null)) ||
  29.                           max(decode(piece,3,sql_text,null))
  30.                                sql_text
  31.                      from v$sqltext_with_newlines
  32.                     where address = x.sql_address
  33.                       and piece < 4)
  34.         loop
  35.             if ( y.sql_text not like '%listener.get_cmd%' and
  36.                  y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
  37.             then
  38.                 dbms_output.put_line( '--------------------' );
  39.                 dbms_output.put_line( x.username );
  40.                 dbms_output.put_line( x.logon_time || ' ' ||
  41.                                       x.current_time||
  42.                                       ' last et = ' ||
  43.                                       x.LAST_CALL_ET);
  44.                 dbms_output.put_line(
  45.                           substr( y.sql_text, 1, 250 ) );
  46.             end if;
  47.         end loop;
  48.     end loop;
  49. end;
  50. /

  51. column username format a15 word_wrapped
  52. column module format a15 word_wrapped
  53. column action format a15 word_wrapped
  54. column client_info format a30 word_wrapped

  55. select username||'('||sid||','||serial#||')' username,
  56.        module,
  57.        action,
  58.        client_info
  59. from v$session
  60. where module||action||client_info is not null;
转自:
阅读(2433) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~