cat sql.sh
a=$1
sqlplus -s zabbix/zabbix@$a <<!!
declare
text long ;
sql_str long ;
begin
for i in (
select c.username,
a.sql_id,
a.ELAPSED_SECONDS,
b.SQL_FULLTEXT
from v\$session_longops a, v\$sqlarea b,v\$session c
where a.sql_id = b.sql_id
and a.sql_hash_value = b.hash_value
and a.target not like '%SYSTEM%'
and a.ELAPSED_SECONDS > 10
and a.sid = c.sid
and a.SERIAL#= c.SERIAL#
and c.username not in ( 'SYS')
and rownum < 10 )
loop
dbms_output.put_line(to_char(i.SQL_FULLTEXT)) ;
dbms_output.put_line(' ') ;
execute immediate 'alter session set current_schema='||i.username;
execute immediate 'explain plan for '||i.SQL_FULLTEXT;
dbms_output.put_line('username='||i.username||' sql_id='||i.sql_id||' elapsed_seconds='||i.ELAPSED_SECONDS);
for t in ( select PLAN_TABLE_OUTPUT from table(dbms_xplan.display ))
loop
dbms_output.put_line(to_char(t.PLAN_TABLE_OUTPUT)) ;
end loop ;
dbms_output.put_line('############################################################################################################################################');
end loop ;
EXCEPTION WHEN OTHERS THEN
raise ;
end ;
/
有错误请指出,谢谢!