oracle SQL监控脚本-优快云博客

今天看到了一些oracle数据库监控的脚本，为以后监控数据库sql带来便利，因此将其摘录下来。

1.捕获耗费性能的SQL语句
#!/bin/ksh
sqlplus -s /nolog << EOF
connect sys/sys as sysdate;
col sid format 99999
col serial# format 999999
col username format 999999
col program format a10
set lines 1000
set pages 1000
set trimspool on

--获取会话
select sid, serial#, username, program, to_char(logon_time, 'yyyy-mm-dd hh24:mi:ss') as logon_time
from v$session
where paddr in(select addr from v$process where spid = $1);

--获取该会话运行的SQL语句
select sql_text
from v$sqltext_with_newlines
where hash_value in
   (select sql_hash_value
      from v$session
     where paddr in
     (select addr from v$process where spid = $1))
order by piece;

exit;
EOF

2.监控当前活动的语句
sqlplus -s /nolog << EOF
connect sys/sys as sysdba;

set serveroutput on size 1000000
set lines 200
set pages 1000
set feedback off
column username format a72 word_wrapped
column program format a72 word_wrapped
column sql_text format a72 word_wrapped

declare

    --自定义过程，一行内输出250个字符以上
    procedure p(p_str in varchar2)
    is
       l_str long := p_str;
    begin

        loop

            exit when l_str is null;
            dbms_output.put_line(substr(l_str, 1, 250));
            l_str := substr(l_str, 250);
        end loop;

    end;
begin

   for x in (select * from (select a.username || '(' || a.sid || ',' || a.serial# || ') spid = ' || b.spid ||
             ' hash_value = ' || to_char(a.sql_hash_value) username, ||
             ' program = ' || a.program || ' et = ' || last_call_et program, sql_address
             from v$session a,
                  v$process b
            where a.status = 'ACTIVE'
              and a.paddr = b.paddr
              and rawtohex(sql_address) <> '00'
              and a.username is not null
              and sid <> (select sid from v$mystat where rownum = 1)
             order by last_call_et desc)
            where rownum < 10)
loop

     --打印状态信息
     dbms_output.put_line('--------------------');
     dbms_output.put_line(x.username);
     dbms_output.put_line(x.program);

     --打印sql语句
     for y in (select sql_text
                 from v$sqltext_with_newlines
                where address = x.sql_address
                order by piece)
     loop
       p(y.sql_text);
     end loop;

end loop;

end;
/