linux - 使用 bash 脚本创建短和长 sqlplus 连接 X 时间
问题描述
我需要使用带有 bash 脚本的 sqlplus 客户端创建到 oracle db 的短/长连接 10 分钟（为了举例），两者之间的区别如下：
长连接：
- 已建立连接
- 每隔 5-10 秒，连接就会执行某种查询
- 在 10 分钟结束时，客户端被终止
短接：
- 已建立连接
- 进行查询
- 连接已关闭
- 重复该过程 10 分钟（连接打开和关闭，始终保持 10 个连接）
我有一个基线，我需要针对上述场景进行调整（2 个不同的 bash 文件）：
#!/bin/sh
for i in $(seq 1 10);
do
echo "CREATE TABLE oracle_BEQ_$i (id NUMBER NOT NULL);
! sleep 30
select * from oracle_BEQ_$i ;
! sleep 30
DROP TABLE oracle_BEQ_$i;" | sqlplus <user>/<password> &
done
wait
此脚本当前仅执行以下操作：
1）创建10个连接（同时）
2) 运行 3 个查询
3）完成后，连接关闭
对于我提到的 2 个场景，我需要做哪些调整？
解决方案
示例 1.
您可以在后台运行脚本pipe.sh。并在另一个会话中，发送 sql 文件或 sql. 运行sql后重新连接。
more pipe.sh
#!/bin/bash
rm /tmp/sqlplus_pipe.sql
mknod /tmp/sqlplus_pipe.sql p
while :
do
$ORACLE_HOME/bin/sqlplus "system/manager" <<EOF
@/tmp/sqlplus_pipe.sql
EOF
sleep 1
done
在后台运行此脚本
nohup ./pipe.sh >pipe_log.log 2>&1 &
在其他 bash 会话中，您可以将 sql 文件或 sql 发送到此背景进程。
oracle@esmd:/tmp> cat test2.sql >>/tmp/sqlplus_pipe.sql
oracle@esmd:/tmp> cat test2.sql >>/tmp/sqlplus_pipe.sql
oracle@esmd:/tmp> echo "select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;" >>/tmp/sqlplus_pipe.sql
oracle@esmd:/tmp> echo "select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;" >>/tmp/sqlplus_pipe.sql
oracle@esmd:/tmp> more test2.sql
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
oracle@esmd:~> more pipe_log.log
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 14:50:35 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
TO_CHAR(SYSDATE,'DD
-------------------
08-08-2019 14:50:46
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 14:50:47 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
TO_CHAR(SYSDATE,'DD
-------------------
08-08-2019 14:50:48
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 8 14:50:49 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL>
TO_CHAR(SYSDATE,'DD
-------------------
08-08-2019 14:50:49
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
更新。
示例 2.
您可以在 Oracle 数据库服务器的后台运行脚本pipev2.sh。并在另一个会话中，发送 sql 文件或 sql. 运行sql后无需重新连接。
nohup ./pipev2.sh >output.log 2>&1 &
#!/bin/bash
rm /home/trs/db2Toora/sql/sqlplus_pipe.sql
mknod /home/trs/db2Toora/sql/sqlplus_pipe.sql p
$ORACLE_HOME/bin/sqlplus "system/manager" <<EOF
SET SERVEROUTPUT ON
BEGIN
RUN_SQL;
END;
/
EOF
CREATE OR REPLACE DIRECTORY TEMP_DIR_CHANGE AS '/home/trs/db2Toora/sql'
/
GRANT READ ON DIRECTORY TEMP_DIR_CHANGE TO SYSTEM
/
GRANT WRITE ON DIRECTORY TEMP_DIR_CHANGE TO SYSTEM
/
CREATE OR REPALCE PROCEDURE RUN_SQL
is
sql_text VARCHAR2(2000);
file_sql_name VARCHAR2(100):='sqlplus_pipe.sql';
sql_delimiter VARCHAR2(1):=';';
stop_script VARCHAR2(10):='%QUIT%';
sql_output VARCHAR2(2000);
InFile utl_file.file_type;
vNewLine VARCHAR2(4000);
k pls_integer :=0;
BEGIN
dbms_output.enable;
while k <>1
loop
InFile := utl_file.fopen('TEMP_DIR_CHANGE', file_sql_name,'r');
LOOP
BEGIN
utl_file.get_line(InFile, vNewLine);
if vNewLine like '%'||sql_delimiter||'%' then
sql_text:=sql_text||vNewLine;
dbms_output.put_line(sql_text);
begin
execute immediate replace(sql_text,sql_delimiter,'' ) into sql_output;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('!---!--Error--!---!');
dbms_output.put_line(substr(sqlerrm, 1, 500));
end;
dbms_output.put_line(sql_output);
dbms_output.put_line('---------------------------------------------------------------');
sql_text:='';
elsif vNewLine like stop_script then
dbms_output.put_line('---!--QUIT--!---');
EXIT;
else
sql_text:=sql_text||vNewLine;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
if vNewLine like stop_script then
exit;
end if;
end loop;
utl_file.fclose(InFile);
END;
测试
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> cat test2.sql >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql> echo 'QUIT' >sqlplus_pipe.sql
oracle@esmd:/home/trs/db2Toora/sql>
oracle@esmd:/home/trs/db2Toora/sql> more test2.sql
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')
from dual
;
select to_char(sysdate,'DD-MM-YYYY HH24:MI')
from dual
;
nohup: ignoring input
SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 13 10:11:23 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> SQL> 2 3 4 select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
13-08-2019 10:11:34
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI') from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
13-08-2019 10:11:35
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI') from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
13-08-2019 10:11:35
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI') from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
13-08-2019 10:11:36
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI') from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
13-08-2019 10:11:36
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI') from dual;
13-08-2019 10:11
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
13-08-2019 10:11:37
---------------------------------------------------------------
select to_char(sysdate,'DD-MM-YYYY HH24:MI') from dual;
13-08-2019 12:19
---------------------------------------------------------------
---!--QUIT--!---
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
推荐阅读
- python-3.x - 我在尾部函数中的插入节点不会改变尾部的值
- firebase - 无法在 Firebase 中创建用户配置文件
- javascript - Javascript - Making an object and incrementing count in an array for each loop
- r - ggplot2 theme to color inside of polar plot
- c# - Unity：将 json 字符串反序列化为 C# 对象
- android - Porterduff 模式 DST_IN 在路径周围绘制一个矩形
- d3.js - 如何在 d3js 中创建具有路径属性的条形图？
- scala - Scala 语法：为什么匿名函数参数类型使用括号和花括号进行不同的解析？
- html - flexbox 项目中不受欢迎的换行行为
- ios - xcode - stop audio files from playing over each other