undo长事务查询脚本

---创建视图
create or replace view v$sqlarea_total as
select sql_id,sql_text from gv$sqlarea
 union all  select sql_id,to_char(SUBSTR(SQL_TEXT,1,4000)) from Dba_Hist_Sqltext;


---创建function
CREATE OR REPLACE FUNCTION fn_getsqlbysqlid(vSql_ID varchar2) return varchar is
  Result varchar2(4000);
begin
  dbms_output.enable(1000000);
  begin
    for x in (Select substr(sql_text, 1, 4000) sql_text
                from V$sqlarea_Total t --v$sqlarea t
               where t.sql_id = vSql_ID
                 and rownum = 1) loop
      Result := Result || x.sql_text;
    End loop;
  End;
  return(substr(Result, 1, 4000));
end fn_getsqlbysqlid;


---查询undo使用较多的sql语句
select begin_time,
       end_time,
       fn_getsqlbysqlid(maxqueryid),
       maxqueryid,
       t.TUNED_UNDORETENTION,
       trunc(end_time, 'mi') - trunc(begin_time, 'mi') as exec_time_min
       /*t.**/
  from v$undostat t
 order by exec_time_min desc;