0 Replies Latest reply on Dec 2, 2014 5:22 PM by user12005535

    How to view SQL Details Active Report generated out of dbms_sqltune.Report_sql_detail package?

    user12005535

      For example the following will generate the output to display the SQL Details Activity report for the Top 1 SQL (order by elapsed time) for schema : scott.

      SET LONG 1000000;
      SET LONGCHUNKSIZE 1000000;
      SET LINESIZE 1000;
      SET PAGESIZE 0;
      SET TRIM ON;
      SET TRIMSPOOL ON;
      SET ECHO OFF;
      SET FEEDBACK OFF;

      spool ok.html
      WITH long_running_jobs
      AS (SELECT *
      FROM (SELECT sql_id,
      last_call_et_hr,
      Row_number ()
      over (
      ORDER BY last_call_et_hr DESC) top_5
      FROM (SELECT DISTINCT ses.sql_id,
      Floor (ses.last_call_et / 3600) AS
      last_call_et_hr
      FROM gv$session ses,
      gv$sql SQL
      WHERE
      SES.STATUS = 'ACTIVE'
      AND ses.username IS NOT NULL
      AND ses.audsid != Userenv ('SESSIONID')
      AND ses.sql_address = SQL.address
      AND ses.sql_hash_value = SQL.hash_value
      AND ses.username IN ( 'SCOTT')
      ORDER BY last_call_et_hr DESC))
      WHERE top_5 = 1)
      SELECT sys.dbms_sqltune.Report_sql_detail (sql_id => rj.sql_id, TYPE => 'ACTIVE',
      report_level => 'ALL') AS report
      FROM dual,
      long_running_jobs rj
      ;
      spool off;

       

      I am trying to display the gui report on SQL Devleoper without executing the spool file.   The sys.dbms_sqltune.Report_sql_detail will generate the output with html tags.

       

      Thanks