$more get_active_sql.sh
sqlplus -s /nolog <connect user/pass

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, 251);
end loop;
end;
-- 獲得狀態為ACTIVE的列表資訊
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.addr
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;
/

exit
EOF

呼叫方法：
$./get_active_sql.sh

指令碼：監控當前活動的語句

相關文章