shell oracle换行,SHELL脚本调用SQLPLUS输出结果 有两个小问题解决不了!

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