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

export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"LOGFILE=/home/oracle/dbscripts/logs/Auto_Sql_Moniter_Report_`date +%F_%H_%M`.txtexec >> $LOGFILE$ORACLE_HOME/bin/sqlplusDBA_MONITER/DBA_MONIT..

轩辕姐姐

885人浏览 · 2021-04-11 14:04:15

轩辕姐姐 · 2021-04-11 14:04:15 发布

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

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/60 AND c.CI_SHA256='d86b903cb76a471aa970efe4c88b9f47fdb5ea120d7cc450f31c287f5bf1efe24772f20829defe21dd89d6343fdf29a4b1df71493b124bdab47f1be40f4aecb6796788528826034928717be5efd3da6e' )

Global Information

------------------------------

Status : DONE (ALL ROWS)

Instance ID : 1

Session : OSSC (399:24293)

SQL ID : 2q60gw0dszcp5

SQL Execution ID : 16777216

Execution Started : 01/17/2017 10:03:17

First Refresh Time : 01/17/2017 10:03:21

Last Refresh Time : 01/17/2017 10:03:28

Duration : 11s

Module/Action : JDBC Thin Client/-

Service : SYS$USERS

Program : JDBC Thin Client

Fetch Calls : 1

Global Stats

=================================================================

=================================================================

| 11 | 0.60 | 10 | 1 | 20176 | 19762 | 154MB |

=================================================================

SQL Plan Monitoring Details (Plan Hash Value=1923086297)

==================================================================================================================================================================================

==================================================================================================================================================================================

| 0 | SELECT STATEMENT | | | | 1 | +11 | 1 |

1 | | | | |

| 1 | SORT AGGREGATE | | 1 | | 1 | +11 | 1 | 1 | | | | |

| 2 | NESTED LOOPS | | | | 1 | +11 | 1 | 6 | | | | |

| 3 | NESTED LOOPS | | 1 | 13 | 1 | +11 | 1 | 6 | | | | |

| 4 | TABLE ACCESS BY INDEX ROWID | CCPS_CREDITINFO | 1 | 10 | 11 | +1 | 1 | 89 | 15598 | 122MB | 100.00 | db file sequential read (11) |

| 5 | INDEX RANGE SCAN | IX_CI_SHA256 | 5 | 5 | 8 | +4 | 1 | 19509 | 999 | 8MB | | |

| 6 | INDEX UNIQUE SCAN | PK_CCPS_TRADERECORD | 1 | 2 | 1 | +11 | 115 | 6 | 24 | 192KB | | |

| 7 | TABLE ACCESS BY INDEX ROWID | CCPS_TRADERECORD | 1 | 3 | 1 | +11 | 6 | 6 | | | | |

==================================================================================================================================================================================

问题一有SQL>提示符存在虽然设置了set SQLPROMPT '' 好像对匿名块不生效

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

问题二是执行计划居然有换行了无论设置 set wrap on 还是OFF 都是无法控制它

SQL Plan Monitoring Details (Plan Hash Value=1923086297)

==================================================================================================================================================================================

==================================================================================================================================================================================

| 0 | SELECT STATEMENT | | | | 1 | +11 | 1 |

1 | | | | |

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

如何快速上手 hvac：HashiCorp Vault Python 客户端零基础入门指南

**hvac** 是 HashiCorp Vault 的 Python 3.X 客户端库，专为开发者提供简单高效的 Vault 交互方式。无论你是需要管理密钥、配置身份验证，还是实现安全的秘密数据存储，hvac 都能帮助你轻松搞定 Vault 的各项操作。本文将带你零基础快速入门，从安装到基础操作，让你在几分钟内即可上手使用这个强大的工具。[![hvac 客户端 Logo](https://r

魔乐社区

提升Angular2-HN性能的7个实用技巧：让新闻加载速度飞起来

Angular2-HN是一款基于Angular构建的Progressive Hacker News客户端，专为追求高效新闻浏览体验的用户设计。本文将分享7个实用技巧，帮助你优化Angular2-HN的性能，让新闻加载速度显著提升，带来更流畅的阅读体验。## 1. 启用Service Worker缓存关键资源Service Worker是提升Angular应用性能的强大工具，它可以在后台缓存