sqlmon.sql
SQL_IDからSQLモニターレポート等を出力する
code:SQL
-- @sqlmon fknbv84341g3p
set long 1000000
set longc 1000000
set lines 500
set trimspool on
set pages 0
define
col fname1 new_value spool_file1 for a100
col fname2 new_value spool_file2 for a100
col fname3 new_value spool_file3 for a100
col fname4 new_value spool_file4 for a100
select 'HTML_&1'||'.html' fname1 from dual;
select 'TEXT_&1' fname2 from dual;
select 'SQL_&1' fname3 from dual;
select 'PLAN_&1' fname4 from dual;
spool &spool_file1
select SYS.dbms_sqltune.report_sql_monitor (sql_id => '&1',type => 'HTML',report_level => 'ALL') from dual;
spool off
spool &spool_file2
select SYS.dbms_sqltune.report_sql_monitor (sql_id => '&1',type => 'TEXT',report_level => 'ALL') from dual;
spool off
spool &spool_file3
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID = '&1';
select SQL_FULLTEXT from gv$sqlarea where SQL_ID = '&1';
spool off
spool &spool_file4
-- SELECT * FROM table (DBMS_XPLAN.DISPLAY_AWR('&1',null,null,'ADVANCED'));
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&1',null));
spool off