export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
LOGFILE=/home/oracle/dbscripts/logs/Auto_Sql_Moniter_Report_`date +%F_%H_%M`.txt
exec >> $LOGFILE
$ORACLE_HOME/bin/sqlplus DBA_MONITER/DBA_MONITER <
set long 100000
set pagesize 1000
set linesize 2500
set feedback off
set timing off
set termout off
set serveroutput on size 1000000
set tab OFF
SET ECHO OFF
set wrap on
set heading off
SET SQLNUMBER OFF
set SQLPROMPT ''
col report format a10000
declare
v_Report varchar(32767);
n_sql_num number(10) ;
v_sql_id varchar(32) ;
begin
select count(distinct sql_id) into n_sql_num from v\$sql_monitor;
if n_sql_num > 0 then
for i in (select distinct sql_id from v\$sql_monitor) loop
begin
SELECT DBMS_SQLTUNE.report_sql_monitor(
sql_id => i.sql_id,
type => 'TEXT',
report_level => 'ALL') AS report into v_Report
FROM dual;
DBMS_OUTPUT.put_line(v_Report);
END;
end loop;
end if;
end;
/
exit;
EOF复制代码执行结果如下:
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 1月 17 11:05:54 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 SQL Monitoring Report
SQL Text
------------------------------
SELECT count(1) totCount , sum(case when t.TR_STATUS = 1 then 1 else 0 end) sucCount , sum(case when t.TR_STATUS = 0 then 1 else 0 end) failedCount from CCPS_TRADERECORD t where t.TR_NO in ( SELECT c.CI_TR_NO from CCPS_CREDITINFO c where c.CI_DATETIME > TO_DATE('2017-01-16 10:02:31' ,'YYYY-MM-DD HH24:MI:SS')+15/24