一、最简单的shell里调用sqlplus.$ vi test1.sh#!/bin/bash
sqlplus -S /nolog > result.log < set heading off feedback off pagesize 0 verify off echo off
conn u_test/iamwangnc
select * from tab;
exit
EOF$ chmod +x test1.sh
$ ./test1.sh二、把sqlplus执行结果传递给shell方法一注意sqlplus段使用老板键`了, 赋变量的等号两侧不能有空格.$ vi test2.sh#!/bin/bash
VALUE=`sqlplus -S /nolog < set heading off feedback off pagesize 0 verify off echo off numwidth 4
conn u_test/iamwangnc
select count(*) from tab;
exit
EOF`
if [ "$VALUE" -gt 0 ]; then
echo "The number of rows is $VALUE."
exit 0
else
echo "There is no row in the table."
fi$ chmod +x test2.sh
$ ./test2.sh三、把sqlplus执行结果传递给shell方法二注意sqlplus段使用 col .. new_value .. 定义了变量并带参数exit, 然后自动赋给了shell的$?$ vi test3.sh#!/bin/bash
sqlplus -S /nolog > result.log < set heading off feedback off pagesize 0 verify off echo off numwidth 4
conn u_test/iamwangnc
col coun new_value v_coun
select count(*) coun from tab;
exit v_coun
EOF
VALUE="$?"
echo "The number of rows is $VALUE."$ chmod +x test3.sh
$ ./test3.sh四、把shell程序参数传递给sqlplus$1表示第一个参数, sqlplus里可以直接使用, 赋变量的等号两侧不能有空格不能有空格.$ vi test4.sh#!/bin/bash
NAME="$1"
sqlplus -S u_test/iamwangnc < select * from tab where tname = upper('$NAME');
exit
EOF$ chmod +x test4.sh
$ ./test4.sh ttt五、为了安全要求每次执行shell都手工输入密码$ vi test5.sh#!/bin/bash
echo -n "Enter password for u_test:"
read PASSWD
sqlplus -S /nolog < conn u_test/$PASSWD
select * from tab;
exit
EOF$ chmod +x test5.sh
$ ./test5.sh六、为了安全从文件读取密码对密码文件设置权限, 只有用户自己才能读写.$ echo 'iamwangnc' > u_test.txt
$ chmod g-rwx,o-rwx u_test.txt
$ vi test6.sh#!/bin/bash
PASSWD=`cat u_test.txt`
sqlplus -S /nolog < conn u_test/$PASSWD
select * from tab;
exit
EOF$ chmod +x test6.sh
$ ./test6.sh-
原文地址
http://hi.baidu.com/edeed/blog/item/291698228a5694f4d7cae2c1.html
自己试验的内容:
#!/bin/sh
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_SID=oravm
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/9.2.0
export LD_LIBRARY_PATH=/opt/oracle/9.2.0/lib:/lib:/usr/lib:/usr/local/lib:/usr/X11R6/lib
export TNS_ADMIN=/opt/oracle/9.2.0/network/admin
export ORA_NLS33=/opt/oracle/9.2.0/ocommon/nls/admin/data
export ORACLE_OWNER=oracle
export ORACLE_TERM=xterm
export PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/opt/oracle/9.2.0/bin
export LANG=zh_CN.gb2312
export NLS_LANG="Simplified Chinese_china".ZHS16GBK
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'/*将/home/oracle/archlog/下的全部文件(实际上是归档日志文件)读入系统,结合数据字典,然后写入system.logmnr表中)*/
for fname in /home/oracle/archlog/*; do
echo "Processing $fname!"
echo "connect /as sysdba" > /home/oracle/temp.sql
echo "EXECUTE dbms_logmnr.add_logfile('$fname',dbms_logmnr.addfile);" >> /home/oracle/temp.sql
echo "EXECUTE dbms_logmnr.start_logmnr ( DictFileName=>'/opt/oracle/9.2.0/Oraclelogs/v816dict.ora')" >> /home/oracle/temp.sql
echo "insert into system.logmnr("SCN","CSCN","TIMESTAMP","COMMIT_TIMESTAMP","THREAD#","LOG_ID","XIDUSN","XIDSLT","XIDSQN","PXIDUSN","PXIDSLT","PXIDSQN","RBASQN","RBABLK","RBABYTE","UBAFIL","UBABLK","UBAREC","UBASQN","ABS_FILE#","REL_FILE#","DATA_BLK#","DATA_OBJ#","DATA_OBJD#","SEG_OWNER","SEG_NAME","SEG_TYPE","SEG_TYPE_NAME","TABLE_SPACE","ROW_ID","SESSION#","SERIAL#","USERNAME","SESSION_INFO","TX_NAME","ROLLBACK","OPERATION","OPERATION_CODE","SQL_REDO","SQL_UNDO","RS_ID","SEQUENCE#","SSN","CSF","INFO","STATUS","REDO_VALUE","UNDO_VALUE","SQL_COLUMN_TYPE","SQL_COLUMN_NAME","REDO_LENGTH","REDO_OFFSET","UNDO_LENGTH","UNDO_OFFSET")(select
* from v\$logmnr_contents);" >> /home/oracle/temp.sql
echo "exit;" >> /home/oracle/temp.sql
/opt/oracle/9.2.0/bin/sqlplus /nolog @/home/oracle/temp.sql
echo The SQL file $fname finished!
echo ---------------------------------------------------------------
done
分享到:
相关推荐
在java代码中调用执行shell脚本,sqlldr导数与使用sqlplus在shell调用执行存储过程。 linux环境中有2个dba的用户:oracle、erm 还有1个web用户:erm 在linux环境中,这三个用户都可以直接在任意目录下执行该shell...
NULL 博文链接:https://jin8000608172.iteye.com/blog/1720290
关于sqlplu在Bash_shell上使用技巧
Oracle SQLPLUS基础及sqlplus命令详解,详细阐述了oracle sqlplus基本命令和相关用法
先来看一个简单的利用python调用sqlplus来输出结果的例子: import os import sys from subprocess import Popen, PIPE sql = set linesize 400 col owner for a10 col object_name for a30 select owner, object_...
内容:本章介绍Oracle 10g提供的工具SQL*Plus,并通过一些小的代码对SQL*Plus的一些常用命令进行讲解 产品:Oracle 10g 技术:SQL_Plus 难度:★★☆☆☆
SQLPLUS 常用指令收集与技巧, 改文档中列举了sql脚本中使用的各种sqlplus命令技巧,在编写shell时很有用
这是本人总结的一些sqlplus的常用指令,希望大家喜欢!!!
通常linux下用安装oracle后,只有切换至oracle用户下方可执行相关的数据库的sqlplus及imp\exp相关命令的操作,经过这里的此配置后方可解决此问题,所有用户下均可以执行上述相关操作
$ sudo yum install rlwrap-0.42-1-x64.... stty erase ^h # 解决 shell 下sqlplus 退格问题 alias sqlplus='rlwrap sqlplus' alias rman='rlwrap rman' alias lsnrctl='rlwrap lsnrctl' $ source ./bash_profile
今天小编就为大家分享一篇关于Oracle基础:程序中调用sqlplus的方式,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧
详细介绍了oracle的sqlplus环境及常用的一些命令,并且对命令有详细的注释介绍等!!!
Sqlplus常用的命令,数据库管理员的学习笔记。
sqlplusw 与 sqlplus登陆数据库的方法总结
Oracle SQLPlus详细用法. 压缩文档包括以下3份文件 SQLPlus I SQLPlus II SQLPlus III
sqlplus 常用命令行参数,Oracle 中 sqlplus 初学者用的 常用命令行参数
SQLPLUS命令使用大全SQLPLUS命令使用大全SQLPLUS命令使用大全SQLPLUS命令使用大全
SQLPlus命令,SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令SQLPlus命令
ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全ORACLE SQLPLUS 命令大全
详细介绍ORACLE sqlplus命令,详细介绍ORACLE sqlplus命令